问题描述
从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 )
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。