oracle理论知识_oracle中文手册

理论

Edition-Based Redefinition（EBR）是11gR2的新特性。
在Oracle MAA架构中，常提到在线操作，Edition-Based Redefinition（EBR）即其中一种。EBR可实现代码的升级，包括Synonym，View，Function，Procedure,Package (specification and body)，Type (specification and body)，Library和Trigger。其它的在线操作包括：

• 修改系统参数
• Reorganizing 对象（例如将非分区表转为分区表）
• 创建索引
• RAC滚动升级
• 数据库升级（major release to major release）

注意，表是没有版本的。因为他是有状态对象

在没有EBR之前，对象只有一个版本，通过OWNER.OBJECTNAME引用，在有EBR之后，一个对象有多个版本，可以通过切换会话来使用不同版本。在升级过程中，我们可以在新版本中修改代码，赋权，测试，累积这些变化，最后再数据库层面修改默认版本为新的版本即可。

实验1

实验依照的是Oracle博文：A Closer Look at the New Edition
操作的数据库为PDB：orclpdb1

connect sys/Welcome1@orclpdb1 as sysdba

-- 创建用户DEMO
SQL> create user demo identified by demo;
User created.

-- 赋予连接和创建过程权限
SQL> grant create session, create procedure to demo;
Grant succeeded.

-- 创建新版本version2，作为ORA$BASE的子版本。ORA$BASE是系统默认的版本
SQL> create edition version2 as child of ora$base;
Edition created.

-- 切换到DEMO用户, 此时使用的是默认版本
SQL> connect demo/demo@orclpdb1
Connected.

-- 创建过程my_procedure和my_procedure2
DEMO> create or replace
procedure my_procedure
as
begin
   dbms_output.put_line
   ( 'I am version 1.0' );
end;
  8  /

Procedure created.

DEMO> create or replace
procedure my_procedure2
as
begin
   my_procedure;
end;
  7  /

Procedure created.

-- 默认版本中执行，输出1.0
DEMO> exec my_procedure2
I am version 1.0

PL/SQL procedure successfully completed.

-- 切换到DBA用户
DEMO> connect sys/Welcome1@orclpdb1 as sysdba
Connected.

-- 创建新用户SCOTT
SQL> create user scott identified by tiger;

User created.

SQL> grant create session to scott;

Grant succeeded.

-- 为用户DEMO启用版本
SQL> alter user demo enable editions;

User altered.

-- 允许用户DEMO使用版本version2
SQL> grant use on edition version2 to demo;

Grant succeeded.

-- 允许用户SCOTT使用版本version2
SQL> grant use on edition version2 to scott;

Grant succeeded.

-- 切换到DEMO用户
SQL> connect demo/demo@orclpdb1
Connected.

-- 切换到版本version2
DEMO> alter session set edition = version2;

Session altered.

-- 此时可看到2个过程，集成至默认版本ORA$BASE
DEMO> 
select object_name,
         object_type,
         status,
         edition_name
from user_objects;
OBJECT_NAME      OBJECT_TYPE             STATUS  EDITION_NAME
---------------- ----------------------- ------- ----------------
MY_PROCEDURE     PROCEDURE               VALID   ORA$BASE
MY_PROCEDURE2    PROCEDURE               VALID   ORA$BASE

-- 修改过程my_procedure，版本为2.0
DEMO> create or replace
procedure my_procedure
as
begin
   dbms_output.put_line
   ( 'I am version 2.0' );
end;
  8  /

-- 过程my_procedure已变为VERSION2
DEMO> 
select object_name,
         object_type,
         status,
         edition_name
from user_objects;
OBJECT_NAME      OBJECT_TYPE             STATUS  EDITION_NAME
---------------- ----------------------- ------- ----------------
MY_PROCEDURE2    PROCEDURE               VALID   ORA$BASE
MY_PROCEDURE     PROCEDURE               VALID   VERSION2

-- my_procedure在版本version2中已经实体化了，不再是指向父版本的链接
DEMO>
select object_name,
   edition_name
   from user_objects_AE;

OBJECT_NAME      EDITION_NAME
---------------- ----------------
MY_PROCEDURE     ORA$BASE
MY_PROCEDURE2    ORA$BASE
MY_PROCEDURE     VERSION2

-- 赋予SCOTT用户my_procedure2执行权限
DEMO> grant execute on my_procedure2 to scott;

Grant succeeded.

-- 由于权限变化，这会引发my_procedure2在版本version2中实体化
DEMO>
select object_name,
         object_type,
         status,
         edition_name
    from user_objects;

OBJECT_NAME      OBJECT_TYPE             STATUS  EDITION_NAME
---------------- ----------------------- ------- ----------------
MY_PROCEDURE     PROCEDURE               VALID   VERSION2
MY_PROCEDURE2    PROCEDURE               VALID   VERSION2

DEMO> select object_name,
   edition_name
   from user_objects_AE;

OBJECT_NAME      EDITION_NAME
---------------- ----------------
MY_PROCEDURE     ORA$BASE
MY_PROCEDURE2    ORA$BASE
MY_PROCEDURE     VERSION2
MY_PROCEDURE2    VERSION2

-- 确认当前版本为VERSION2
DEMO> 
SELECT SYS_CONTEXT
  ('userenv',
   'current_edition_name') sc
   FROM DUAL;

SC
--------------------------------------------------------------------------------
VERSION2

-- 确认执行的是新版本的过程
DEMO> exec my_procedure2
I am version 2.0

PL/SQL procedure successfully completed.

-- 切换用户到DEMO
DEMO> connect demo/demo@orclpdb1
Connected.

-- 确认是默认版本
DEMO>
SELECT SYS_CONTEXT
  ('userenv',
   'current_edition_name') sc
  4  FROM DUAL;

SC
--------------------------------------------------------------------------------
ORA$BASE

-- 确认执行的是老版本
DEMO> exec my_procedure2;
I am version 1.0

PL/SQL procedure successfully completed.

-- 切换到SCOTT用户
DEMO> connect scott/tiger@orclpdb1

-- 确认是默认版本
SCOTT> 
SELECT SYS_CONTEXT
  ('userenv',
   'current_edition_name') sc
   FROM DUAL;

SC
--------------------------------------------------------------------------------
ORA$BASE

-- 由于在默认版本里并没有赋权给SCOTT，因此它无法看到存储过程
SCOTT> exec demo.my_procedure2
BEGIN demo.my_procedure2; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DEMO.MY_PROCEDURE2' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

-- 切换到版本version2
SCOTT> alter session set edition = version2;

Session altered.

-- 由于之前赋过执行权限，因此可正常执行
SCOTT> exec demo.my_procedure2
I am version 2.0

PL/SQL procedure successfully completed.

-- 切换到DBA用户
SCOTT> connect sys/Welcome1@orclpdb1 as sysdba
Connected.

-- 在数据库层面将版本修改为VERSION2，需要重连才生效
SQL> alter database default edition = version2;

Database altered.

-- 切换到SCOTT用户
SCOTT> connect scott/tiger@orclpdb1
Connected.

-- 确认执行的是新版本
SCOTT> exec demo.my_procedure2
I am version 2.0

PL/SQL procedure successfully completed.

-- 此时系统默认的版本已变为VERSION2，不再是ORA$BASE
SCOTT>
SELECT SYS_CONTEXT
  ('userenv',
   'current_edition_name') sc
       FROM DUAL;

SC
--------------------------------------------------------------------------------
VERSION2

-- 当然，你也可以将版本改回去
SQL> alter database default edition = ORA$BASE;

实验2

实验依照的是Oracle博文：Edition-Based Redefinition, Part 2
此实验是在实验1的基础上进行。

SQL> connect sys/Welcome1@orclpdb1 as sysdba
-- 赋予用户DEMO新的权限，其余权限在实验1中已经赋予了
SQL> grant create table, create sequence to demo;
Grant succeeded.

SQL> grant select on hr.employees to demo;
Grant succeeded.

SQL> alter user demo quota unlimited on users;
User altered.

-- 当前的版本是version2
DEMO>
SELECT SYS_CONTEXT
  ('userenv',
   'current_edition_name') sc
           FROM DUAL;

SC
--------------------------------------------------------------------------------
VERSION2

-- 创建一个新的版本version3, 并赋权给用户demo
SQL> create edition version3 as child of version2;
Edition created.
SQL> grant use on edition version3 to demo;
Grant succeeded.

-- 创建editioning view 权限
SQL> grant create view to demo;
Grant succeeded.

-- 创建crossedition trigger权限
SQL> grant create trigger to demo;
Grant succeeded.

-- 执行Job权限
SQL> grant create job to demo;
Grant succeeded.

SQL> connect demo/demo@orclpdb1
Connected.

DEMO>
create table
employees
as
select * from hr.employees;

Table created.

DEMO> create sequence emp_seq start with 500;

Sequence created.

-- 创建package，其中过程show用于搜索，函数add用于新增雇员
-- 此package代表我们老版本的应用代码
-- 参数中没有雇员ID，是因为其会使用之前的序列自动生成
SQL>
create or replace package emp_pkg
  as
    procedure show
    ( last_name_like in employees.last_name%type );

    function add
    ( FIRST_NAME in employees.FIRST_NAME%type := null,
      LAST_NAME in employees.LAST_NAME%type,
      EMAIL in employees.EMAIL%type,
      PHONE_NUMBER in employees.PHONE_NUMBER%type := null,
      HIRE_DATE in employees.HIRE_DATE%type,
      JOB_ID in employees.JOB_ID%type,
      SALARY in employees.SALARY%type := null,
      COMMISSION_PCT in employees.COMMISSION_PCT%type := null,
      MANAGER_ID in employees.MANAGER_ID%type := null,
      DEPARTMENT_ID in employees.DEPARTMENT_ID%type := null  )
    return employees.employee_id%type;
  end;
  /
Package created.

-- 创建Package Body
DEMO>
create or replace package body emp_pkg
  as

  procedure show
  ( last_name_like in employees.last_name%type )
  as
  begin
      for x in
      ( select first_name, last_name,
               phone_number, email
          from employees
         where last_name like
               show.last_name_like
         order by last_name )
      loop
          dbms_output.put_line
          ( rpad( x.first_name || ' ' ||
                    x.last_name, 40 ) ||
            rpad( nvl(x.phone_number, ' '), 20 ) ||
            x.email );
      end loop;
  end show;

  function add
  ( FIRST_NAME in employees.FIRST_NAME%type := null,
    LAST_NAME in employees.LAST_NAME%type,
    EMAIL in employees.EMAIL%type,
    PHONE_NUMBER in employees.PHONE_NUMBER%type := null,
    HIRE_DATE in employees.HIRE_DATE%type,
    JOB_ID in employees.JOB_ID%type,
    SALARY in employees.SALARY%type := null,
    COMMISSION_PCT in employees.COMMISSION_PCT%type := null,
    MANAGER_ID in employees.MANAGER_ID%type := null,
    DEPARTMENT_ID in employees.DEPARTMENT_ID%type := null
  )
  return employees.employee_id%type
  is
      employee_id  employees.employee_id%type;
  begin
      insert into employees
      ( EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
        EMAIL, PHONE_NUMBER, HIRE_DATE,
        JOB_ID, SALARY, COMMISSION_PCT,
        MANAGER_ID, DEPARTMENT_ID )
      values
      ( emp_seq.nextval, add.FIRST_NAME, add.LAST_NAME,
        add.EMAIL, add.PHONE_NUMBER, add.HIRE_DATE,
        add.JOB_ID, add.SALARY, add.COMMISSION_PCT,
        add.MANAGER_ID, add.DEPARTMENT_ID )
      returning employee_id into add.employee_id;

      return add.employee_id;
  end add;

  end;
  /
Package body created.

-- 测试搜索函数
DEMO> set serveroutput on
DEMO> exec emp_pkg.show( '%K%' );
Payam Kaufling                          650.123.3234        PKAUFLIN
Alexander Khoo                          515.127.4562        AKHOO
Steven King                             515.123.4567        SKING
Janette King                            011.44.1345.429268  JKING
Neena Kochhar                           515.123.4568        NKOCHHAR
Sundita Kumar                           011.44.1343.329268  SKUMAR

PL/SQL procedure successfully completed.

-- 新增员工Tom Kyte，然后在搜索
SQL>
begin
    dbms_output.put_line
    ( emp_pkg.add
      ( first_name      => 'Tom',
        last_name       => 'Kyte',
        email           => 'TKYTE',
        phone_number    => '703.123.9999',
        hire_date       => sysdate,
        job_id          => 'IT_PROG' ) );
  end;
 11    /
500

PL/SQL procedure successfully completed.

DEMO> exec emp_pkg.show( '%K%' );
Payam Kaufling                          650.123.3234        PKAUFLIN
Alexander Khoo                          515.127.4562        AKHOO
Janette King                            011.44.1345.429268  JKING
Steven King                             515.123.4567        SKING
Neena Kochhar                           515.123.4568        NKOCHHAR
Sundita Kumar                           011.44.1343.329268  SKUMAR
Tom Kyte                                703.123.9999        TKYTE

PL/SQL procedure successfully completed.

-- 建立Edition View，这是唯一引起中断的地方。rt表示Real Table.
DEMO> alter table employees rename to employees_rt;
Table altered.

create editioning view employees
  as
  select 
    EMPLOYEE_ID, FIRST_NAME,
    LAST_NAME, EMAIL, PHONE_NUMBER,
    HIRE_DATE, JOB_ID, SALARY,
    COMMISSION_PCT, MANAGER_ID,
    DEPARTMENT_ID
  from employees_rt
 /

-- 之后，所有的操作都基于Edition View，包括增删改查，例如：
DEMO> delete from employees where employee_id=500;
DEMO> rollback;

-- 好，现在可以修改schema了
-- 从11gR1起，增加列和索引都是在线操作
-- 增加两列，原始的phone_number列仍在，但edition view屏蔽了这两列
DEMO>
alter table employees_rt
  add
  ( country_code varchar2(3),
    phone# varchar2(20)
  )
  /
Table altered.

-- 添加索引，在线但不可见，因此不会被使用。
DEMO>
create index employees_phone#_idx
  on employees_rt(phone#)
  ONLINE INVISIBLE
  /
Index created.

-- 切换到版本version3
DEMO> alter session set edition = version3;
Session altered.

-- 创建cross edition trigger
-- 注意这个trigger是作用在employees_rt表的，他使得老的应用依旧可以运行，同时将改变传递到新的表结构中，注意forward crossedition关键字。
SQL>
create or replace trigger employees_fwdxedition
  before insert or update of phone_number on employees_rt
  for each row
  forward crossedition
  declare
      first_dot  number;
      second_dot number;
  begin
      if :new.phone_number like '011.%'
     then
          first_dot
             := instr( :new.phone_number, '.' );
          second_dot
             := instr( :new.phone_number, '.', 1, 2 );
          :new.country_code
             := '+'||
                substr( :new.phone_number,
                          first_dot+1,
                        second_dot-first_dot-1 );
          :new.phone#
             := substr( :new.phone_number,
                          second_dot+1 );
      else
          :new.country_code := '+1';
          :new.phone# := :new.phone_number;
      end if;
  end;
  /
Trigger created.

-- 目前位置，由于没有做数据迁移，新的表结构中的两个字段都是空值
DEMO> select count(*) from employees_rt where country_code is null;

  COUNT(*)
----------
       108

-- 执行以下语句模拟老版本(version)应用，检验数据已迁移成功
DEMO> select count(*) from employees;

  COUNT(*)
----------
       108

DEMO> select count(*) from employees_rt where country_code is null;

  COUNT(*)
----------
         0
DEMO> select phone_number, phone#, country_code from employees_rt where rownum < 5;

PHONE_NUMBER         PHONE# COU
-------------------- -------------------- ---
650.507.9833         650.507.9833         +1
650.507.9844         650.507.9844         +1
515.123.4444         515.123.4444         +1
515.123.5555         515.123.5555         +1

-- 以上方法虽然成功，但是会锁整个表，下面介绍一种并行方法，可以只锁表的一部分
-- 为此，我们先将之前的修改回退
DEMO> update employees_rt set phone#=null, country_code=null;

108 rows updated.
DEMO> commit;

Commit complete.
DEMO> select count(*) from employees_rt where country_code is null;

  COUNT(*)
----------
       108

-- 这种方法利用了DBMS_PARALLEL_EXECUTE包，即将表按照rowid分为多个部分，然后并行对这些部分操作
-- 为测试并行，我们先将表放大100倍
DEMO>
insert into employees
select * from
(
with data(r)
as
(select 1 r from dual
 union all
 select r+1 from data where r <= 100
)
select rownum+(select max(employee_id)
                      from employees_rt),
       FIRST_NAME, LAST_NAME, EMAIL,
       PHONE_NUMBER, HIRE_DATE, JOB_ID,
       SALARY, COMMISSION_PCT, MANAGER_ID,
       DEPARTMENT_ID
  from employees_rt, data
);
10908 rows created.

-- 查看数据
select phone_number, country_code, phone#
  from employees_rt
 where country_code is null
   and rownum <= 5
 union all
select phone_number, country_code, phone#
  from employees_rt
 where country_code is NOT null
  9     and rownum <= 5;

PHONE_NUMBER         COU PHONE#
-------------------- --- --------------------
650.507.9833
650.507.9844
515.123.4444
515.123.5555
603.123.6666
650.507.9876         +1  650.507.9876
650.507.9877         +1  650.507.9877
650.507.9878         +1  650.507.9878
650.507.9879         +1  650.507.9879
650.509.1876         +1  650.509.1876

10 rows selected.
-- 以下的数据，两个新增的列是空的
DEMO> select count(*) from employees_rt where country_code is null;

  COUNT(*)
----------
       108
-- 以下的数据，两个新增的列已填充值
DEMO> select count(*) from employees_rt where country_code is not null;

  COUNT(*)
----------
     10908
-- 放大后的表占用137个块
DEMO> select count(*), count(distinct dbms_rowid.rowid_block_number(rowid)) cnt_blk from employees;

  COUNT(*)    CNT_BLK
---------- ----------
     11016        137

-- 创建任务，将表分区，每chunk_size为10
begin
    dbms_parallel_execute.create_task('update employees_rt');
    dbms_parallel_execute.create_chunks_by_rowid
    ( task_name   => 'update employees_rt',
      table_owner => user,
      table_name  => 'EMPLOYEES_RT',
      by_row      => false,
      chunk_size  => 10);
end;
/

PL/SQL procedure successfully completed.
-- 结果是将表分成了6部分
select chunk_id, status, start_rowid, end_rowid
  from user_parallel_execute_chunks
 where task_name = 'update employees_rt'
   -- and rownum <= 5
  5  /

  CHUNK_ID STATUS               START_ROWID        END_ROWID
---------- -------------------- ------------------ ------------------
         1 UNASSIGNED           AAASP3AAMAAAJnwAAA AAASP3AAMAAAJo5H//
         2 UNASSIGNED           AAASP3AAMAAAJo6AAA AAASP3AAMAAAJqDH//
         3 UNASSIGNED           AAASP3AAMAAAJqEAAA AAASP3AAMAAAJsVH//
         4 UNASSIGNED           AAASP3AAMAAAJsWAAA AAASP3AAMAAAJtHH//
         5 UNASSIGNED           AAASP3AAMAAAJtIAAA AAASP3AAMAAAJt5H//
         6 UNASSIGNED           AAASP3AAMAAAJt6AAA AAASP3AAMAAAJt/H//

6 rows selected.

-- 执行并行任务，更新两个字段
begin
    dbms_parallel_execute.run_task
    ( task_name      => 'update employees_rt',
      sql_stmt       => 'update employees_rt set phone_number = phone_number where rowid between :start_id and :end_id',
      language_flag  => DBMS_SQL.NATIVE,
      parallel_level => 2 );
end;
/

PL/SQL procedure successfully completed.
-- 验证全部更新完毕
DEMO> select count(*) from employees_rt where country_code is not null;

  COUNT(*)
----------
     11016

DEMO> select count(*) from employees_rt;

  COUNT(*)
----------
     11016

-- 至此，数据迁移已无问题，以下将迁移应用
-- 为此，先切换到新版本version3
DEMO> alter session set edition = version3;

Session altered.

-- 目前，所有的代码均继承至version2
DEMO>
select object_name, object_type, status, edition_name
  from user_objects_ae
  where object_name in ( 'EMPLOYEES', 'EMP_PKG' );

OBJECT_NAME  OBJECT_TYPE             STATUS  EDITION_NAME
------------ ----------------------- ------- ------------
EMPLOYEES    VIEW                    VALID   VERSION2
EMP_PKG      PACKAGE                 VALID   VERSION2
EMP_PKG      PACKAGE BODY            VALID   VERSION2

-- 创建新版本的edition view，大写是改动的代码，我们已经使用了新的schema
create OR REPLACE editioning view employees
  as
  select
    employee_id, first_name,
    last_name, email, COUNTRY_CODE, PHONE#,
    hire_date, job_id, salary,
    commission_pct, manager_id,
    department_id
  from employees_rt
 /

View created.

-- Package声明中，修改PHONE_NUMBER部分
create or replace package emp_pkg
  as
    procedure show
    ( last_name_like in employees.last_name%type );

    function add
    ( FIRST_NAME in employees.FIRST_NAME%type := null,
      LAST_NAME in employees.LAST_NAME%type,
      EMAIL in employees.EMAIL%type,
      -- 替换部分
      COUNTRY_CODE in employees.COUNTRY_CODE%type := null,
	  PHONE# in employees.PHONE#%type := null,
	  -- 替换部分
      HIRE_DATE in employees.HIRE_DATE%type,
      JOB_ID in employees.JOB_ID%type,
      SALARY in employees.SALARY%type := null,
      COMMISSION_PCT in employees.COMMISSION_PCT%type := null,
      MANAGER_ID in employees.MANAGER_ID%type := null,
      DEPARTMENT_ID in employees.DEPARTMENT_ID%type := null  )
    return employees.employee_id%type;
  end;
  /
Package created.

-- 修改package body
create or replace package body emp_pkg
  as

  procedure show
  ( last_name_like in employees.last_name%type )
  as
  begin
      for x in
      ( select first_name, last_name,
               country_code, phone#, email
          from employees
         where last_name like
               show.last_name_like
         order by last_name )
      loop
          dbms_output.put_line
          ( rpad( x.first_name || ' ' ||
                    x.last_name, 40 ) ||
            rpad( nvl(x.country_code, ' '), 5 ) ||
            rpad( nvl(x.phone#, ' '), 20 ) ||
            x.email );
      end loop;
  end show;

  function add
  ( FIRST_NAME in employees.FIRST_NAME%type := null,
    LAST_NAME in employees.LAST_NAME%type,
    EMAIL in employees.EMAIL%type,
    COUNTRY_CODE in employees.COUNTRY_CODE%type := null,
    PHONE# in employees.PHONE#%type := null,
    HIRE_DATE in employees.HIRE_DATE%type,
    JOB_ID in employees.JOB_ID%type,
    SALARY in employees.SALARY%type := null,
    COMMISSION_PCT in employees.COMMISSION_PCT%type := null,
    MANAGER_ID in employees.MANAGER_ID%type := null,
    DEPARTMENT_ID in employees.DEPARTMENT_ID%type := null
  )
  return employees.employee_id%type
  is
      employee_id  employees.employee_id%type;
  begin
      insert into employees
      ( EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
        EMAIL, COUNTRY_CODE, PHONE#, HIRE_DATE,
        JOB_ID, SALARY, COMMISSION_PCT,
        MANAGER_ID, DEPARTMENT_ID )
      values
      ( emp_seq.nextval, add.FIRST_NAME, add.LAST_NAME,
        add.EMAIL, add.COUNTRY_CODE, add.PHONE#, add.HIRE_DATE,
        add.JOB_ID, add.SALARY, add.COMMISSION_PCT,
        add.MANAGER_ID, add.DEPARTMENT_ID )
      returning employee_id into add.employee_id;

      return add.employee_id;
  end add;

  end;
  /
Package body created.

-- 这时新版本代码已实体化了
select object_name, object_type, status, edition_name
  from user_objects_ae
  3   where object_name in ( 'EMPLOYEES', 'EMP_PKG' );

OBJECT_NAME  OBJECT_TYPE             STATUS  EDITION_NAME
------------ ----------------------- ------- ------------
EMPLOYEES    VIEW                    VALID   VERSION2
EMP_PKG      PACKAGE                 VALID   VERSION2
EMP_PKG      PACKAGE BODY            VALID   VERSION2
EMPLOYEES    VIEW                    VALID   VERSION3
EMP_PKG      PACKAGE                 VALID   VERSION3
EMP_PKG      PACKAGE BODY            VALID   VERSION3

6 rows selected.

-- 如果运行版本3中的代码，如EMP_PKG.ADD，此时会更新新增的两列，但老的phone_number列则不会更新，因此还需要一个反向crossedition trigger
create or replace trigger employees_revxedition
before insert or update of country_code,phone# on employees_rt
for each row
reverse crossedition
declare
    first_dot  number;
    second_dot number;
begin
        if :new.country_code = '+1'
        then
           :new.phone_number :=
              :new.phone#;
        else
           :new.phone_number :=
              '011.' ||
              substr( :new.country_code, 2 ) ||
              '.' || :new.phone#;
        end if;
end;
/
Trigger created.

-- 执行新版本代码
begin
 dbms_output.put_line
 ( emp_pkg.add
   ( first_name   => 'Tom',
     last_name    => 'Kight',
     email        => 'TKYTE',
     country_code => '+44',
     phone# => '703.123.4567',
     hire_date    => sysdate,
     job_id       => 'IT_PROG' ) );
end;
/
PL/SQL procedure successfully completed.
-- 新版本下的代码显示了两个新字段
DEMO> exec emp_pkg.show( 'Kight' );
Tom Kight                               +44  703.123.4567        TKYTE

PL/SQL procedure successfully completed.
-- 切换到老版本，老版本代码显示了老的1个字段
DEMO> connect demo/demo@orclpdb1
Connected.
orclpdb1> set serveroutput on
orclpdb1> exec emp_pkg.show( 'Kight' );
Tom Kight                               011.44.703.123.4567 TKYTE

PL/SQL procedure successfully completed.

-- 为使应用平滑切换，我们让老的会话仍使用老的版本，新的会话使用新的版本，为此我们使用了AFTER LOGON trigger
SQL> grant use on edition version3 to public;

Grant succeeded.

create or replace trigger set_edition_on_logon
after logon on database
begin
        dbms_session.set_edition_deferred( 'VERSION3' );
end;
/
Trigger created.

-- 验证
SQL> connect demo/demo@orclpdb1
Connected.

SQL>
SELECT SYS_CONTEXT
  ('userenv',
   'current_edition_name') sc
     FROM DUAL;

SC
--------------------------------------------------------------------------------
VERSION3
DEMO> exec emp_pkg.show( 'Kight' );
Tom Kight                               +44  703.123.4567        TKYTE

PL/SQL procedure successfully completed.

-- 如果应用迁移成功，最后进入清理阶段
-- 此时可以查询V$SESSION的SESSION_EDITION_ID，来确定会话使用的edition
-- 清理包括删除forward 和reverse crossedition triggers
-- 删除PHONE_NUMBER列

参考

1. A Closer Look at the New Edition
2. Edition-Based Redefinition, Part 2
3. Looking at Edition-Based Redefinition, Part 3
4. Edition-Based Redefinition白皮书
5. Online Data Reorganization and Redefinition
6. Edition-Based Redefinition (EBR)
7. Online Table Redefinition (DBMS_REDEFINITION) Enhancements in Oracle Database 11g Release 1
8. 了解Oracle在线重定义Online Redefinition
9. CREATE EDITON帮助

今天的文章oracle理论知识_oracle中文手册分享到此就结束了，感谢您的阅读。