Online Redefinition Partition Existing Table, ora-600 [kkzuord_copycolcomcb.2.exec] and ORA-23539

从9i起可以重定义表结构可以在线，对于在线重定义的好处很多站点都有这里不再叙述，原理也是利用了mview及mview log 的低层操作， 满足对于7*24 小时业务的在线调整， 但是需要增加原大小一倍的空间存放临时数据， 今天业务库有个非分区表存放了近4年的数据，实际保留6个月就可以，需要不停业务的情况下清理掉历史数据并释放空间并换成分区表， 下面我记录一下过程及遇到的意外。
# db version 11.2.0.3.7

SQL> @seg anbob.MSG_T1
    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
     24104 anbob                   MSG_T1                                              TABLE                DATACOMMON                        3085312        183    2127890
SQL> @seg anbob.INX_MSG_T1_TELNUM
    SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK
---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
     19828 anbob                 INX_MSG_T1_TELNUM                                       INDEX                DATA999                           2537984          7     823314
	 
SQL> @ind anbob.MSG_T1
Display indexes where table or index name matches %anbob.MSG_T1%...
TABLE_OWNER          TABLE_NAME            INDEX_NAME                 POS# COLUMN_NAME            DSC
-------------------- ---------------    -------- ------------------------------ ---- ------ ------------ ----
anbob                 MSG_T1               INX_MSG_T1_TELNUM            1   TELNUM

INDEX_OWNER          TABLE_NAME                     INDEX_NAME                     IDXTYPE    UNIQ STATUS   PART TEMP  H     LFBLKS           NDK   NUM_ROWS       CLUF LAST_ANALYZED     DEGREE VISIBILIT
-------------------- ------------------------------ ------------------------------ ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ----------------- ------ ---------
anbob                 MSG_T1                       INX_MSG_T1_TELNUM        NORMAL     NO   VALID    NO   N     4    2161360       6746713  246549980  224716000 20150327 05:51:24 1      VISIBLE
SQL> @desc anbob.MSG_T1
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      TELNUM                          NOT NULL VARCHAR2(25)
    2      PORT                            NOT NULL VARCHAR2(21)
    3      MSGID                                    VARCHAR2(16)
    4      SUBMIT_TIME                              DATE
    5      DONE_TIME                                DATE
    6      STATUS                                   VARCHAR2(8)
    7      INTIME                          NOT NULL DATE

 CREATE TABLE "anbob"."MSG_T1"
   (    "TELNUM" VARCHAR2(25) NOT NULL ENABLE,
        "PORT" VARCHAR2(21) NOT NULL ENABLE,
        "MSGID" VARCHAR2(16),
        "SUBMIT_TIME" DATE,
        "DONE_TIME" DATE,
        "STATUS" VARCHAR2(8) DEFAULT 'DELIVRD',
        "INTIME" DATE DEFAULT sysdate NOT NULL ENABLE,
         SUPPLEMENTAL LOG GROUP "GGS_2078144" ("TELNUM", "PORT", "MSGID", "SUBMIT_TIME", "DONE_TIME", "STATUS", "INTIME") ALWAYS
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 20 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 16384 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATACOMMON" ;
  
  
SQL> @ddl anbob.INX_MSG_T1_TELNUM
PL/SQL procedure successfully completed.
DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OWNER)
----------------------------------------------------------------------------------------------------------------
  CREATE INDEX "anbob"."INX_MSG_T1_TELNUM" ON "anbob"."MSG_T1" ("TELNUM")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 4194304 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA999" ;
  
复制

上面是收集的原表的一些信息，一个24G的表上有个非唯一索引，下面我们创建一相同列的分区表(没有尝试直接创建interval partition不过可以分好后再改如果是11gr2)

 CREATE TABLE anbob.MSG_T1_MID
   (    "TELNUM" VARCHAR2(25) NOT NULL ENABLE,
        "PORT" VARCHAR2(21) NOT NULL ENABLE,
        "MSGID" VARCHAR2(16),
        "SUBMIT_TIME" DATE,
        "DONE_TIME" DATE,
        "STATUS" VARCHAR2(8) DEFAULT 'DELIVRD',
        "INTIME" DATE DEFAULT sysdate NOT NULL ENABLE,
         SUPPLEMENTAL LOG GROUP "GGS_20781441" ("TELNUM", "PORT", "MSGID", "SUBMIT_TIME", "DONE_TIME", "STATUS", "INTIME") ALWAYS
   ) partition by range(INTIME)
( partition p1 values less than (to_date('2014-12-01','YYYY-MM-DD')) tablespace DATA315,
  partition p2 values less than (to_date('2015-01-01','YYYY-MM-DD')) tablespace DATA315,
  partition p3 values less than (to_date('2015-02-01','YYYY-MM-DD')) tablespace DATA314,
  partition p4 values less than (to_date('2015-03-01','YYYY-MM-DD')) tablespace DATA314,
  partition p5 values less than (to_date('2015-04-01','YYYY-MM-DD')) tablespace DATABUDATA,
  partition p6 values less than (to_date('2015-05-01','YYYY-MM-DD')) tablespace DATABUDATA,
  partition p7 values less than (to_date('2015-06-01','YYYY-MM-DD')) tablespace DATABUDATA,
  partition p8 values less than (to_date('2015-07-01','YYYY-MM-DD')) tablespace DATABUDATA,
  partition p9 values less than (to_date('2015-08-01','YYYY-MM-DD')) tablespace DATA317,
  partition p10 values less than (to_date('2015-09-01','YYYY-MM-DD')) tablespace DATA317,
  partition p11 values less than (maxvalue) tablespace users);
复制

先收集原表统一信息

exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'anbob' , tabname => 'MSG_T1', estimate_percent => 5,method_opt=>'for all columns size 1', granularity => 'ALL', degree => 8,no_invalidate=>false);
复制

会话级增加并行加快速度

alter session force parallel dml parallel 8;
alter session force parallel query parallel 8;
alter session set "_sort_multiblock_read_count"=128;
alter session set db_file_multiblock_read_count=512;
复制

因为无主键，这里使用基于rowid的(不过还有伪主键的)
验证

exec dbms_redefinition.can_redef_table('anbob','MSG_T1',DBMS_REDEFINITION.cons_use_rowid);
复制

开始同步数据

SQL> exec dbms_redefinition.start_redef_table('anbob','MSG_T1','MSG_T1_MID', options_flag=>DBMS_REDEFINITION.cons_use_rowid);
BEGIN dbms_redefinition.start_redef_table('anbob','MSG_T1','MSG_T1_MID', options_flag=>DBMS_REDEFINITION.cons_use_rowid); END;
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkzuord_copycolcomcb.2.exec], [], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_REDEFINITION", line 56
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1490
ORA-06512: at line 1
SQL> select object_name, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;  
no rows selected
SQL> SELECT OWNER ,MVIEW_NAME,REFRESH_METHOD,COMPILE_STATE FROM dba_mviews;
OWNER                          MVIEW_NAME                     REFRESH_ COMPILE_STATE
------------------------------ ------------------------------ -------- -------------------
anbob                           MSG_T1_MID                     FAST     ERROR
SQL> SELECT * FROM DBA_MVIEW_LOGS;
no rows selected
复制

没想到最后报出了ora-600,查询是bug 导致12765293 ，原因是表或列上有comment, 清掉所有comment
查询COMMENT

 select * from dba_col_comments where table_name='MSG_T1'
 select * from dba_TAB_comments where table_name='MSG_T1';
 
SQL> exec dbms_redefinition.start_redef_table('anbob','MSG_T1','MSG_T1_MID', options_flag=>DBMS_REDEFINITION.cons_use_rowid);
BEGIN dbms_redefinition.start_redef_table('anbob','MSG_T1','MSG_T1_MID', options_flag=>DBMS_REDEFINITION.cons_use_rowid); END;
*
ERROR at line 1:
ORA-23539: table "anbob"."MSG_T1" currently being redefined
ORA-06512: at "SYS.DBMS_REDEFINITION", line 56
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1490
ORA-06512: at line 1
复制

如果再次重启复制或者dbms_redefinition.can_redef_table 都会出错，重做前需要终止定义, 手动删除mview

 SQL> execute dbms_redefinition.abort_redef_table ('anbob','MSG_T1','MSG_T1_MID');
 
 SQL> drop materialized  view anbob.MSG_T1_MID;
复制

现重新开始就可以

SQL> exec dbms_redefinition.can_redef_table('anbob','MSG_T1',DBMS_REDEFINITION.cons_use_rowid);
PL/SQL procedure successfully completed.
SQL> exec dbms_redefinition.start_redef_table('anbob','MSG_T1','MSG_T1_MID', options_flag=>DBMS_REDEFINITION.cons_use_rowid);

DECLARE
    num_errors PLS_INTEGER;
  BEGIN
    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname            => 'anbob',
                                            orig_table       => 'MSG_T1',
                                            int_table        => 'MSG_T1_MID',
                                            copy_indexes     => 1,
                                            copy_triggers    => TRUE,
                                            copy_constraints => FALSE,
                                            copy_privileges  => TRUE,
                                            ignore_errors    => FALSE,
                                            num_errors       => num_errors,
                                            copy_statistics  => TRUE);
  END;
  /
  
exec dbms_redefinition.finish_redef_table('anbob','MSG_T1','MSG_T1_MID');
SQL> select name from col$ where obj#=958501;
NAME
------------------------------
DONE_TIME
INTIME
MSGID
PORT
STATUS
SUBMIT_TIME
SYS_C00008_15072116:44:20$
TELNUM
复制

如果从col$基表查询转换为分区的表上会有个隐藏列SYS_xxx，而且在11g r2 就是unused, 只需要执行下面的脚本清除。

  alter table anbob.MSG_T1 drop unused column;
复制

剩下的清理历史数据就不再演示。

alter table xx drop partition xx update global index;
复制

最后再删掉原来的中间表即可。

What can be Redefined ONLINE on a Table?
 => A non-partitioned table can be converted into a partitioned table, and   vice versa
 => The organization of a table can be changed from a heap based to IOTs (Index   Organized Tables), and vice versa
 => Non-primary key columns can be dropped
 => New columns can be added 
 => Existing columns can be renamed ,modified
 => Parallel support can be added or removed
 => Storage parameters can be modified
 => Move a table or cluster to a different tablespace
 => Change partition structure
 => Add support for parallel queries
 => Re-create a table or cluster to reduce fragmentation
Restrictions AND Noties
------------
The table to be re-organized:
  * Must have a primary key (restriction should have been lifted in 9.2.0.5. It is possible that there is still a problem with this.)
  * Cannot have User-defined data types
  * Cannot have FILE or LONG columns
  * Cannot be clustered
  * Cannot be in the SYS or SYSTEM schema
  * Cannot have materialized view logs and/or   materialized views defined on them
  * Cannot be an horizontal subsetting of data
  * Must be re-organized within the same schema
  * Looses its snapshot logs
  * Can get new columns as part of the re-organization, but the  new columns must be declared NULL until the re-organization    is complete
  * If table is empty(no segment) and deferred_segment_creation is enabled in 11.2, an ORA-4020 Object Deadlock error occurs when attempting to FINISH_REDEF. 
  * Online Redefinition Leaves Global Index Unusable(Bug7174085.The bug is fixed in 11.2.)
  * Desc table not showing constraints(Bug 16023293)
  * ORA-600 [kkzuord_copycolcomcb.2.prepare] or [kkzuord_copycolcomcb.2.exec] During DBMS_REDEFINITION of Table with Comments (Bug 12765293 )
复制