在线重定义表(dbms_redefinition)

 

在线重定义表(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;