在线重定义表(Redefine Tables Online) - dbms_redefinition

Oracle9i开始引入在线重定义表(Redefine Tables Online)的功能,通过调用dbms_redefinition包，可以在修改表结构的同时允许DML操作。

在线重定义表的主要功能：

修改表的存储参数
在同一个schema下将表移动到不同的tablespace
增加并行查询支持
添加或删除分区支持
重建表以减少碎片
将堆表变为索引组织表或相反
添加或删除列
在线重定义表的需要以下权限：

execute_catalog_role
create any table
alter any table
drop any table
lock any table
select any table
在线重定义后的结果:

原表已经根据中间表的结构重新定义
在start_redef_table()和finish_redef_table()之间定义在中间表上的触发器、索引、约束和授权，现在定义在原始重定义表上。中间表上

disabled的约束在原始表上处于enabled状态。
原始表上定义的触发器、索引、约束和授权建立在中间表上，并会在删除中间表时删除。原始表上原来enabled状态的索引，建立在中间表上，并处

于disabled状态。
任何定义在原始表上的存储过程和游标都会变为INVALID，当下次调用时后自动进行编译。
如果执行过程中出现错误或者人为选择退出的话，可以执行DBMS_REDEFINITION.ABORT_REDEF_TABLE()过程。
在线重定义的一些限制：

如果使用基于主键的方式，则原表后重定义后的表必须有相同的主键
如果使用基于ROWID的方式，则不能是索引组织表
如果原表上有物化视图或者物化视图日志，则不能在线重定义
物化视图容器表或者高级队列表不能在线重定义
索引组织表的溢出表不能在线重定义
拥有BFILE，LOGN列的表不能在线重定义
Cluster中的表不能在线重定义
sys和system下的表不能在线重定义
临时表不能在线重定义
不支持水平数据子集
在列映射时只能使用有确定结果的表达式，如子查询就不行
如果中间表有新增列，则不能有NOT NULL约束
原表和中间表之间不能有引用完整性
在线重定义无法采用nologging

在线重定义表的步骤：

1.选择在线重定义的方法
方法一:基于主键。要求原表和重定义后的表有相同的主键列。这是默认方法
方法二:基于ROWID。该方法不能用于索引组织表(IOT)，并且在重定义的表中会添加隐藏列(M_ROW$$),建议将该列标记为unused或删除。

2.调用dbms_redefinition.can_redef_table()，检查原表是否能进行在线重定义。
SQL> conn / as sysdba
已连接。

SQL> exec dbms_redefinition.can_redef_table(user,'TEST');
BEGIN dbms_redefinition.can_redef_table(user,'TEST'); END;

*
第 1 行出现错误:
ORA-12087: 在 "SYS" 拥有的表上不允许联机重新定义
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 137
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 1478
ORA-06512: 在 line 1

SQL> create user mxb identified by mxb;

用户已创建。

SQL> grant create session to mxb;

授权成功。

SQL> grant execute_catalog_role to ning;

授权成功。

SQL> conn mxb/mxb
已连接。

SQL> create table test(id int,name varchar2(20));

表已创建。

SQL> exec dbms_redefinition.can_redef_table(user,'TEST');
BEGIN dbms_redefinition.can_redef_table(user,'TEST'); END;

*
第 1 行出现错误:
ORA-12089: 不能联机重新定义无主键的表 "NING"."TEST"
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 137
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 1478
ORA-06512: 在 line 1

SQL> alter table test add constraint con_test_pk primary key(id);

表已更改。

SQL> exec dbms_redefinition.can_redef_table(user,'TEST');

PL/SQL 过程已成功完成。

Ok,表ning.test符合使用基于主键方式的在线重定义功能了。

3.在原表同一个schema下建一个空的中间表。根据重定义后你期望得到的结构建立中间表。比如：采用分区表，增加了COLUMN等。
SQL> create table test1(id int,name varchar2(20),address varchar(30));

表已创建。

4.调用dbms_redefinition.start_redef_table()
PROCEDURE START_REDEF_TABLE
参数名称 类型 输入/输出默认值?
------------------------- ---------------------- ----------------------- ------ --------
UNAME VARCHAR2 IN
ORIG_TABLE VARCHAR2 IN
INT_TABLE VARCHAR2 IN
COL_MAPPING VARCHAR2 IN DEFAULT
OPTIONS_FLAG BINARY_INTEGER IN DEFAULT
ORDERBY_COLS VARCHAR2 IN DEFAULT
PART_NAME VARCHAR2 IN DEFAULT
主要参数：
ORIG_TABLE:原表
INT_TABLE:中间表
COL_MAPPING:列的映射关系。默认所有包括在中间表中的列用于表的重定义。如果给出了映射方法，则只考虑映射方法中给出的列
OPTIONS_FLAG:重定义的方法，默认基于主键。dbms_redefinition.cons_use_pk和dbms_redefinition.cons_use_rowid

SQL> exec dbms_redefinition.start_redef_table(user,'TEST','TEST1');
BEGIN dbms_redefinition.start_redef_table(user,'TEST','TEST1'); END;

*
第 1 行出现错误:
ORA-01031: 权限不足
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 50
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: 在 line 1

SQL> conn / as sysdba
已连接。

SQL> grant create any table to mxb;

授权成功。

SQL> grant alter any table to mxb;

授权成功。

SQL> grant drop any table to mxb;

授权成功。

SQL> grant lock any table to mxb;

授权成功。

SQL> grant select any table to mxb;

授权成功。

SQL> conn mxb/mxb
已连接。

SQL> exec dbms_redefinition.start_redef_table(user,'TEST','TEST1');

PL/SQL 过程已成功完成。

5.在中间表上建立触发器、索引和约束，并进行相应的授权。任何包含中间表的引用约束应将状态置为disabled。当重定义完成时，中间表上建立的

触发器、索引、约束和授权将替换重定义表上的触发器、索引、约束和授权。中间表上disabled的约束将在重定义表上enable。
SQL> create index ix_test on test1(name);

索引已创建。

6.(可选)如果在执行dbms_redefinition.start_redef_table()过程和执行dbms_redefinition.finish_redef_table()过程直接在重定义表上执行了

大量的DML操作，那么可以选择执行一次或多次的sync_interim_table()过程，以减少最后一步执行finish_redef_table()过程时的锁定时间。因为

执行finish_redef_table()时会将原表锁定一小段时间，如果大量DML操作存在，会时锁定时间加长。
SQL> exec dbms_redefinition.sync_interim_table(user,'TEST','TEST1');

PL/SQL 过程已成功完成。

7.执行dbms_redefinition.finish_redef_table()，完成重定义，中间表变成原重定义表，原表则变成了中间表
SQL> exec dbms_redefinition.finish_redef_table(user,'TEST','TEST1');

PL/SQL 过程已成功完成。

SQL> desc test
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------

ID NUMBER(38)
NAME VARCHAR2(20)
ADDRESS VARCHAR2(30)
SQL> desc test1
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------

ID NOT NULL NUMBER(38)
NAME VARCHAR2(20)

8.(可选)可以重命名索引、触发器和约束。对于采用了ROWID方式重定义的表，包括了一个隐含列M_ROW$$。推荐使用下列语句经隐含列置为UNUSED状

态或删除。
ALTER TABLE TABLE_NAME SET UNUSED (M_ROW$$);
ALTER TABLE TABLE_NAME DROP UNUSED COLUMNS;

 

--------------------------------------------

注意 ，  在线重定义表使用  create  table xxxx   as   select  *　from xxx ;   建立的表中字段的默认值是不能建立的 ，即所有字段都没有默认值  。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-681493/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-681493/