表的在線重定義方法介紹及使用DBMS_REDEFINITION包案例

1、表的在線重定義方法介紹及使用DBMS_REDEFINITION包案例隨著技術(shù)的發(fā)達(dá)，業(yè)務(wù)的要求也越來(lái)越高，一般的數(shù)據(jù)庫(kù)都會(huì)承載7X24不間隔業(yè)務(wù)運(yùn)行。我們有時(shí)候需要對(duì)表做一些結(jié)構(gòu)上的調(diào)整，比如增加/刪除一個(gè)字段，修改一個(gè)字段等DDL操作，而這種操作對(duì)于7X24運(yùn)行的業(yè)務(wù)的話，如果采用方法不對(duì)，可能對(duì)數(shù)據(jù)庫(kù)就是一個(gè)災(zāi)難。以下列出解決此問(wèn)題的方法并詳細(xì)介紹DBMS_REDEFINITION包的功能。1、 直接使用alter table的方法。2、 創(chuàng)建臨時(shí)表，刪除原表，改表名，創(chuàng)建索引/約束/觸發(fā)器。3、 使用DBMS_REDEFINITION包進(jìn)行。第1種方法最明顯的優(yōu)點(diǎn)就是操作簡(jiǎn)單，一條命令就

2、搞定，節(jié)約空間，不需要額外存儲(chǔ)。但是對(duì)于小表來(lái)說(shuō)可行，對(duì)于大業(yè)務(wù)的大表來(lái)說(shuō)，有可能就是一場(chǎng)災(zāi)難，因?yàn)樵谑褂眠@個(gè)方法的時(shí)候會(huì)對(duì)整個(gè)表加排他鎖，會(huì)導(dǎo)致所有對(duì)該表的操作均處于等待，而大表做此操作往往需要很長(zhǎng)的時(shí)間，長(zhǎng)時(shí)間對(duì)表加排他鎖，其他操作的等待會(huì)一直累積，造成的后果小則業(yè)務(wù)不響應(yīng)，大則系統(tǒng)hang住，所以此法需要慎用，僅用于小業(yè)務(wù)的小表上。第2種方法解決了第1種方法的絕大多數(shù)問(wèn)題，表數(shù)據(jù)僅在刪除原表和改表名之間不可用，而這個(gè)過(guò)程非常短，所以其解決了對(duì)表加排他鎖的問(wèn)題。但是其操作相對(duì)比較復(fù)雜，需要提前做好每步的內(nèi)容；需要即時(shí)將約束和觸發(fā)器建上，不然可能影響業(yè)務(wù)的正確性和一致性；在創(chuàng)建索引完成之前，

3、整個(gè)表的訪問(wèn)均走全表掃描，可能會(huì)導(dǎo)致大量的IO，從而影響業(yè)務(wù)執(zhí)行效率；需要至少2倍表大小的存儲(chǔ)空間，在創(chuàng)建臨時(shí)表時(shí)，原表也同時(shí)存在。這種方法可以用于業(yè)務(wù)量不是很大的表上，不過(guò)需要提前準(zhǔn)備好創(chuàng)建索引，約束，觸發(fā)器的腳本！第3種方法是ORACLE自帶的一種工具包，專門用于進(jìn)行表的重構(gòu)。該種方法中和了前2種方法的優(yōu)點(diǎn)，能最小化對(duì)系統(tǒng)的影響，僅在最后完成重定義一步會(huì)對(duì)表加排他鎖，而這一步耗時(shí)也相對(duì)較小。該方法需要至少2倍表和索引大小的存儲(chǔ)空間，相對(duì)第2步增加了索引的存儲(chǔ)空間。并且需要DBA給予用戶執(zhí)行DBMS_REDEFINITION包的權(quán)限。在大業(yè)務(wù)的大表上請(qǐng)使用此種方法。下面詳細(xì)介紹一下如何使用D

4、BMS_REDEFINITION包進(jìn)行表的重構(gòu)。大體步驟為：1）準(zhǔn)備工作包括表結(jié)構(gòu)，表記錄數(shù)，表存儲(chǔ)大小，表所在的表空間大小，表上的索引，表上的約束，表上的觸發(fā)器，觸發(fā)器中表的數(shù)據(jù)，失效對(duì)象2）在線重定義2.1）檢查是否可以在線重定義2.2）創(chuàng)建中間表2.3）開(kāi)始重定義2.4）拷貝依賴對(duì)象2.5）完成重定義3）核查工作包括表結(jié)構(gòu)，表記錄數(shù)，表存儲(chǔ)大小，表所在表空間大小，表上的索引，表上的約束，表上的觸發(fā)器，觸發(fā)器中表的數(shù)據(jù)，失效對(duì)象下面展示一個(gè)真實(shí)的案例：1、 準(zhǔn)備工作a) 查看表的基本信息desc WMCADMIN.COLORRINGINFO_RELATIONselect count(1)

5、from WMCADMIN.COLORRINGINFO_RELATION;select TO_CHAR(dbms_metadata.get_ddl(TABLE,COLORRINGINFO_RELATION,WMCADMIN) FROM DUAL;b) 查看表以及索引情況select bytes/1024/1024,tablespace_name from dba_segments where owner=WMCADMIN AND SEGMENT_NAME=COLORRINGINFO_RELATION;select idx.owner,index_name,index_type,IDX.tabl

6、espace_name,bytes/1024/1024,status from dba_indexes idx, dba_segments seg where idx.owner=seg.owner and idx.index_name=seg.segment_name and idx.owner=WMCADMIN AND IDX.TABLE_NAME=COLORRINGINFO_RELATION;總共需要空間：表空間還剩select sum(bytes)/1024/1024 from dba_free_space where tablespace_name=BUSIDATA0;表空間剩余足夠

7、。c) 查看約束和觸發(fā)器情況select constraint_name,constraint_type,SEARCH_CONDITION,status from dba_constraints where owner=WMCADMIN AND TABLE_NAME=COLORRINGINFO_RELATION;select trigger_name,trigger_type,triggering_event,status from dba_triggers where owner=WMCADMIN AND TABLE_NAME=COLORRINGINFO_RELATION;select te

8、xt from dba_source where owner=WMCADMIN and name = TRI_COLORRINGINFO_RELATIONselect count(1) from wmcadmin.colorringinfo_relation_add;select count(1) from wmcadmin.colorringinfo_relation_add2;d) 查看失效對(duì)象情況select count(1) from dba_objects where status=INVALID;select owner,object_name from dba_objects w

9、here status=INVALID;SELECT * FROM DBA_REDEFINITION_OBJECTS;SELECT * FROM DBA_REDEFINITION_ERRORS;2、在線重定義2.1、檢查是否可以進(jìn)行在線重定義EXEC DBMS_REDEFINITION.can_redef_table(WMCADMIN, COLORRINGINFO_RELATION);2.2、創(chuàng)建中間表DESC WMCADMIN.COLORRINGINFO_RELATION_TMPcreate table wmcadmin.COLORRINGINFO_RELATION_TMP AS SELEC

10、T * FROM WMCADMIN.COLORRINGINFO_RELATION WHERE 1=2;select TO_CHAR(dbms_metadata.get_ddl(TABLE,COLORRINGINFO_RELATION_TMP,WMCADMIN) FROM DUALalter table wmcadmin.COLORRINGINFO_RELATION_TMP add (marketing_bal varchar2(1) default 0 not null);2.3、開(kāi)始重定義ALTER SESSION FORCE PARALLEL DML PARALLEL 4;ALTER SE

11、SSION FORCE PARALLEL QUERY PARALLEL 4;EXEC DBMS_REDEFINITION.start_redef_table(WMCADMIN,COLORRINGINFO_RELATION,COLORRINGINFO_RELATION_TMP);select MARKETING_BAL from WMCADMIN.COLORRINGINFO_RELATION_TMP where rownum=1;select count(1) from WMCADMIN.COLORRINGINFO_RELATION_TMP;2.4、拷貝依賴對(duì)象DECLARE RETVAL NU

12、MBER (5); BEGIN DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS (WMCADMIN, COLORRINGINFO_RELATION, COLORRINGINFO_RELATION_TMP,IGNORE_ERRORS=TRUE,NUM_ERRORS= RETVAL); DBMS_OUTPUT.PUT_LINE (RETVAL); END; /SELECT * FROM DBA_REDEFINITION_OBJECTSSELECT * FROM DBA_REDEFINITION_ERRORS2.5完成重定義EXEC DBMS_REDEFINITION

13、.finish_redef_table(WMCADMIN, COLORRINGINFO_RELATION, COLORRINGINFO_RELATION_TMP);2、 核查工作a) 檢查表結(jié)構(gòu)desc WMCADMIN.COLORRINGINFO_RELATIONselect TO_CHAR(dbms_metadata.get_ddl(TABLE,COLORRINGINFO_RELATION,WMCADMIN) FROM DUAL;select count(1) from WMCADMIN.COLORRINGINFO_RELATIONselect sum(bytes)/1024/1024 f

14、rom dba_free_space where tablespace_name=BUSIDATA0;b) 檢查索引select idx.owner,index_name,index_type,IDX.tablespace_name,bytes/1024/1024,status from dba_indexes idx, dba_segments seg where idx.owner=seg.owner and idx.index_name=seg.segment_name and idx.owner=WMCADMIN AND IDX.TABLE_NAME=COLORRINGINFO_REL

15、ATIONc) 檢查約束和觸發(fā)器select constraint_name,constraint_type,SEARCH_CONDITION,status from dba_constraints where owner=WMCADMIN AND TABLE_NAME=COLORRINGINFO_RELATIONselect trigger_name,trigger_type,triggering_event,status from dba_triggers where owner=WMCADMIN AND TABLE_NAME=COLORRINGINFO_RELATION;d) 檢查失效對(duì)

16、象select count(1) from dba_objects where status=INVALID;select owner,object_name from dba_objects where status=INVALID;select table_name,trigger_name,trigger_type,triggering_event,status from dba_triggers where owner=WMCADMIN AND trigger_name=TRI_COLORRINGINFO_RELATION;alter trigger WMCADMIN.TRI_COLORRINGINFO_RELATION compile;select owner,object_name,object_type from dba_objects where status=INVALID;select table_name,trigger_name,trigger_type,triggering_event,status from dba_triggers where owner=WMCADMIN AND trigger_name=