oracle自9i开始提供了一个在线重定义的功能,能在线完成对一个表结构或存储的重定义,并且不影响当前应用的使用,是oracle高可用的一个很有用的特性。
在线重定义存在两种定义方法,一种是基于主键,另外一种是基于ORWID。ROWID的方式不能用于索引组织表,而且重定义后会增加一隐藏列M_ROW$$,重建之后需要手动删除该列。一般默认采用主键的方式。
在线重定义步骤
1、调用dbms_redefinition.can_redef_table()来判断当前表是否允许重定义
2、调用dbms_redefinition.start_redef_table()过程来开始在线重定义。过程定义如下:
PROCEDURE start_redef_table
(uname IN VARCHAR2,----用户名
orig_table IN VARCHAR2,----源表名
int_table IN VARCHAR2,----中间表名
col_mapping IN VARCHAR2 := NULL,---源表和中间表列之间的映射,map;
options_flag IN BINARY_INTEGER := 1,---重定义方式 1表示主键重定义 2表示ROWID重定义
orderby_cols IN VARCHAR2 := NULL,---对于分区表重定义的时候,分区列名
part_name IN VARCHAR2 := NULL);---对于分区表重定义的时候,需要重定义的分区。其中最后2个参数没用到,因为这里是由普通表转为分区表时用到的
3、Dbms_Redefinition.sync_interim_table()过程来同步在在线重定义过程中对表进行dml操作的数据
4、Dbms_Redefinition.finish_redef_table()过程结束在线重定义,此过程还会执行一次同步操作,所以第三步骤有时可省略,在这个过程中,原始表会被独占模式锁定一小段时间,具体时间和表的数据量有关。过程执行完后,中间表会变成原定义表,原表会编程中间表。
示例:
1、基于主键重定义
SQL> create table ou_1 as select * from dba_objects;
Table created
SQL> alter table ou_1 add primary key(object_id);
Table altered
SQL> create table ou_2 as select * from dba_objects where 1=2;
Table created
SQL> alter table ou_2 add primary key(object_id);
Table altered
SQL> Select 'ou_1' table_name, count(*) from ou_1
2 union all
3 Select 'ou_2' table_name,count(*) from ou_2;
TABLE_NAME COUNT(*)
---------- ----------
ou_1 51405
ou_2 0
SQL> exec Dbms_Redefinition.can_redef_table('SCOTT','OU_1',dbms_redefinition.cons_use_pk);
PL/SQL procedure successfully completed
SQL> exec Dbms_Redefinition.start_redef_table('SCOTT','OU_1','OU_2');
PL/SQL procedure successfully completed
SQL> select owner, mview_name from User_Mviews;
OWNER MVIEW_NAME
------------------------------ ------------------------------
SCOTT OU_2
SQL> select log_owner,master,log_table from user_mview_logs;
LOG_OWNER MASTER LOG_TABLE
------------------------------ ------------------------------ ------------------------------
SCOTT OU_1 MLOG$_OU_1
SQL> exec Dbms_Redefinition.finish_redef_table('SCOTT', 'OU_1', 'OU_2');
PL/SQL procedure successfully completed
SQL> select owner, mview_name from User_Mviews;
OWNER MVIEW_NAME
------------------------------ ------------------------------
SQL> Select 'ou_1' table_name, count(*) from ou_1
2 union all
3 Select 'ou_2' table_name,count(*) from ou_2;
TABLE_NAME COUNT(*)
---------- ----------
ou_1 51405
ou_2 51405
在线重定义start之后会创建一个物化视图,物化视图跟中间表名称相同,但是2个不同的对象。其次重定义结束之后,物化视图则会被删除。
2、ROWID重定义
SQL> create table ou_1 as select * from dba_objects;
Table created
SQL> create table ou_2 as select * from dba_objects where 1=2;
Table created
SQL>
SQL> Select 'ou_1' table_name, count(*) from ou_1
2 union all
3 Select 'ou_2' table_name,count(*) from ou_2;
TABLE_NAME COUNT(*)
---------- ----------
ou_1 51409
ou_2 0
SQL> exec dbms_redefinition.can_redef_table('SCOTT','OU_1',DBMS_REDEFINITION.CONS_USE_PK);
begin dbms_redefinition.can_redef_table('SCOTT','OU_1',DBMS_REDEFINITION.CONS_USE_PK); end;
ORA-12089: 不能联机重新定义无主键的表 "SCOTT"."OU_1"
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 137
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 1478
ORA-06512: 在 line 2
SQL> exec dbms_redefinition.can_redef_table('SCOTT','OU_1',DBMS_REDEFINITION.cons_use_rowid);
PL/SQL procedure successfully completed
SQL> exec dbms_redefinition.start_redef_table('SCOTT', 'OU_1', 'OU_2');
begin dbms_redefinition.start_redef_table('SCOTT', 'OU_1', 'OU_2'); end;
ORA-12089: 不能联机重新定义无主键的表 "SCOTT"."OU_1"
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 50
ORA-06512: 在 "SYS.DBMS_REDEFINITION", line 1343
ORA-06512: 在 line 2
SQL> exec dbms_redefinition.start_redef_table('SCOTT', 'OU_1', 'OU_2','',2);
PL/SQL procedure successfully completed
SQL> insert into ou_1 select * from dba_objects where rownum =1;
1 row inserted
SQL> commit
2 ;
Commit complete
SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT', 'OU_1', 'OU_2');
PL/SQL procedure successfully completed
以上示例表名ROWID方式在线重定义时,在调用dbms_redefinition.can_redef_table()过程时需指定参数为DBMS_REDEFINITION.CONS_USE_PK,其次在调用dbms_redefinition.start_redef_table时同样需要指定参数options_flag=2,否则报错。在线重定义结束后,查看表结构如下:
SQL> select col#,name,type# from SYS.COL$ WHERE OBJ#=(select object_id from dba_objects where object_name='OU_1' and owner = 'SCOTT');
COL# NAME TYPE#
---------- ------------------------------ ----------
1 OWNER 1
2 OBJECT_NAME 1
3 SUBOBJECT_NAME 1
4 OBJECT_ID 2
5 DATA_OBJECT_ID 2
6 OBJECT_TYPE 1
7 CREATED 12
8 LAST_DDL_TIME 12
9 TIMESTAMP 1
10 STATUS 1
11 TEMPORARY 1
12 GENERATED 1
13 SECONDARY 1
0 SYS_C00014_13041515:44:43$ 1
14 rows selected
此时可发现原表多了一列SYS_C00014_13041515:44:43$,而中间表是没有增加隐藏列的 。注意的是当通过desc tablename查看表字段时是查不到隐藏列的。
如何删除隐藏列
SQL> alter table ou_1 set unused ("SYS_C00014_13041515:44:43$");
Table altered
SQL> alter table ou_1 drop unused columns;
Table altered。