关于在线重定义表

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

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

在线重定义表的需要以下权限：

• execute_catalog_role
• create any table
• alter any table
• drop any table
• lock any table
• select any table

在线重定义表的步骤：

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 ning identified by ning;

用户已创建。

SQL> grant connect,resource to ning;

授权成功。

SQL> grant execute_catalog_role to ning;

授权成功。

SQL> conn ning/ning
已连接。

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 ning;

授权成功。

SQL> grant alter any table to ning;

授权成功。

SQL> grant drop any table to ning;

授权成功。

SQL> grant lock any table to ning;

授权成功。

SQL> grant select any table to ning;

授权成功。

SQL> conn ning/ning
已连接。

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;

在线重定义后的结果:

• 原表已经根据中间表的结构重新定义
• 在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