首页 » 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
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。