首页 » ORACLE 9i-23ai » Online Redefinition Partition Existing Table, ora-600 [kkzuord_copycolcomcb.2.exec] and ORA-23539

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 )

从oracle 12c 新入了新特性

在线重定议支持rollback
在start_redef_table存储过程中使用enable_rollback 参数可以允许在执行Finish_redef_table 前执行rollback操作,回滚所有定义。

SQL> desc dbms_redefinition

...
PROCEDURE START_REDEF_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 UNAME                          VARCHAR2                IN
 ORIG_TABLE                     VARCHAR2                IN
 INT_TABLE                      VARCHAR2                IN
 COL_MAPPING                    VARCHAR2                IN     DEFAULT
 OPTIONS_FLAG                   BINARY_INTEGER          IN     DEFAULT
 ORDERBY_COLS                   VARCHAR2                IN     DEFAULT
 PART_NAME                      VARCHAR2                IN     DEFAULT
 CONTINUE_AFTER_ERRORS          BOOLEAN                 IN     DEFAULT
 COPY_VPD_OPT                   BINARY_INTEGER          IN     DEFAULT
 REFRESH_DEP_MVIEWS             VARCHAR2                IN     DEFAULT
 ENABLE_ROLLBACK                BOOLEAN                 IN     DEFAULT
...

PROCEDURE ROLLBACK
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 UNAME                          VARCHAR2                IN
 ORIG_TABLE                     VARCHAR2                IN
 INT_TABLE                      VARCHAR2                IN     DEFAULT
 PART_NAME                      VARCHAR2                IN     DEFAULT
 DML_LOCK_TIMEOUT               BINARY_INTEGER          IN     DEFAULT
 CONTINUE_AFTER_ERRORS          BOOLEAN                 IN     DEFAULT


PROCEDURE ABORT_ROLLBACK
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 UNAME                          VARCHAR2                IN
 ORIG_TABLE                     VARCHAR2                IN
 INT_TABLE                      VARCHAR2                IN     DEFAULT
 PART_NAME                      VARCHAR2                IN     DEFAULT

PROCEDURE FINISH_REDEF_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 UNAME                          VARCHAR2                IN
 ORIG_TABLE                     VARCHAR2                IN
 INT_TABLE                      VARCHAR2                IN
 PART_NAME                      VARCHAR2                IN     DEFAULT
 DML_LOCK_TIMEOUT               BINARY_INTEGER          IN     DEFAULT
 CONTINUE_AFTER_ERRORS          BOOLEAN                 IN     DEFAULT
 DISABLE_ROLLBACK               BOOLEAN                 IN     DEFAULT

监控

从 12.2 开始,引入了一个新的字典性能视图 V$ONLINE_REDEF,它有助于监控正在进行的在线表重新定义。此视图标识在查询时处理重新定义的当前阶段。

SQL> @desc V$ONLINE_REDEF
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      SID                                      NUMBER
    2      SERIAL#                                  NUMBER
    3      REDEFINITION_ID                          NUMBER
    4      TABLE_OWNER                              VARCHAR2(129)
    5      ORIGINAL_TABLE_NAME                      VARCHAR2(129)
    6      INTERIM_TABLE_NAME                       VARCHAR2(1024)
    7      PARTITION_NAME                           VARCHAR2(1024)
    8      OPERATION                                VARCHAR2(128)
    9      SUBOPERATION                             VARCHAR2(128)
   10      DETAILED_MESSAGE                         VARCHAR2(1024)
   11      PROGRESS                                 VARCHAR2(128)
   12      REFRESH_STATEMENT_SQL_ID                 VARCHAR2(128)
   13      REFRESH_STATEMENT                        VARCHAR2(4000)
   14      CON_ID                                   NUMBER

打赏

, ,

对不起,这篇文章暂时关闭评论。