在线重定义表(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
方法一:基于主键。要求原表和重定义后的表有相同的主键列。
方法二:基于ROWID。该方法不能用于索引组织表(IOT),并且在重定义的表中会添加隐藏列(M_ROW$$),建议将该列标记为unused或删除。
下面是基于方法一做的测试:
C:\Users\yanan>sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 8月 3 14:05:14 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> conn /as sysdba
已连接。
SQL> grant create session to test;
授权成功。
SQL> grant create any table to test;
授权成功。
SQL> grant alter any table to test;
授权成功。
SQL> grant drop any table to test;
授权成功。
SQL> grant lock any table to test;
授权成功。
SQL> grant select any table to test;
授权成功。
SQL> grant execute_catalog_role to test;
授权成功。
SQL> conn test/test@orcl
已连接。
SQL> create table t_redefinition(id number,name varchar2(20));
表已创建。
SQL> insert into t_redefinition(id,name) values(10001,'测试');
已创建 1 行。
SQL> commit;
提交完成。
SQL>
SQL> select * from t_redefinition;
ID NAME
---------- --------------------
10001 测试
SQL> alter table t_redefinition add constraint t_redefinitition_pk primary key(i
d);
表已更改。
SQL> exec DBMS_REDEFINITION.CAN_REDEF_TABLE('test','t_redefinition');
PL/SQL 过程已成功完成。
SQL> create table t_redefinition_test(id number,name varchar2(20));
表已创建。
SQL> exec dbms_redefinition.start_redef_table('test','t_redefinition','t_redefin
ition_test');
PL/SQL 过程已成功完成。
SQL> exec dbms_redefinition.finish_redef_table('test','t_redefinition','t_redefi
nition_test');
PL/SQL 过程已成功完成。
SQL> select * from t_redefinition_test;
ID NAME
---------- --------------------
10001 测试
SQL> rename t_redefinition to t_redefinition_t;
表已重命名。
SQL> rename t_redefinition_test to t_redefinition;
表已重命名。
SQL> drop table t_redefinition_t;
表已删除。
SQL>
(可选)可以重命名索引、触发器和约束。对于采用了ROWID方式重定义的表,包括了一个隐含列M_ROW$$。推荐使用下列语句经隐含列置为UNUSED状态或删除。
ALTER TABLE TABLE_NAME SET UNUSED (M_ROW$$);
ALTER TABLE TABLE_NAME DROP UNUSED COLUMNS;