oracle在线重定义

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。