在10g以前，雖然支援聯機重定義表，但是聯機重定義後的後續工作還有很多，比如建立觸發器、索引、約束等等，當一個表包含很多依賴物件時，這個後續工作就非常耗費精力時間，並且很容易漏掉一些東西。
值得慶幸的是，10g後這個都不會成為我們的負擔，10g的dbms_redefinition新提供一個過程COPY_TABLE_DEPENDENTS，可以幫我們做這件事情。[@more@]下面以聯機把一個非分割槽錶轉換為分割槽表為例說明步驟：

--1. create table
--這一個建立一個與源表結構一樣的臨時表
drop table PROD_USER.PO_ITEMS_TEMP purge;
create table PROD_USER.PO_ITEMS_TEMP tablespace ALLOC_MED
partition by range(creation_date)
(
PARTITION PBI_20090110 VALUES LESS THAN(TO_DATE('2009-01-11','yyyy-mm-dd'))
)
as select * from PO_ITEMS where rownum<1;

--2. re-definition
--開始聯機重定義表

--首先檢查源表是否支援聯機重定義
exec dbms_redefinition.can_redef_table('PROD_USER','PO_ITEMS');

--如果支援，開始聯機重定義
exec DBMS_REDEFINITION.start_redef_table('PROD_USER','PO_ITEMS','PO_ITEMS_TEMP')
--這一步完成後，PO_ITEMS的大部分資料都複製到PO_ITEMS_TEMP了

--因為複製依賴物件時可能需要建立索引，所以把相關引數改大
alter session set workarea_size_policy=manual;
alter session set sort_area_size=1000000000;
alter session set sort_area_retained_size=1000000000;
var n number
exec DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('PROD_USER','PO_ITEMS','PO_ITEMS_TEMP',num_errors=>:n,ignore_errors=>true)
--注意ignore_errors應該設成true，因為在COPY_TABLE_DEPENDENTS時，如果中間表的某些欄位已經非空，則在應用這些非空約束時會出錯，會導致餘下的依賴物件不能複製過去
--可以用print n 看有多少個錯誤，如果n=非空欄位數，則沒有問題
--這一步，oracle會自動把觸發器、索引、約束等相關的依賴物件複製到臨時表中，如：
SYS.ALLOC1CN>select owner,index_name,table_name from dba_indexes where table_name like 'PO_ITEMS%';

OWNER INDEX_NAME TABLE_NAME
------------- ---------------------- ------------------------------
PROD_USER IX_REQUEST_ID PO_ITEMS
PROD_USER IX_SOURCE_ID PO_ITEMS
PROD_USER IX_ITEM_STATUS_ID PO_ITEMS
PROD_USER PK_PBI_ITEM_ID PO_ITEMS
PROD_USER I_PO_ITEMS_VENDOR_DATE PO_ITEMS
PROD_USER TMP$$_IX_REQUEST_ID0 PO_ITEMS_TEMP
PROD_USER TMP$$_IX_SOURCE_ID0 PO_ITEMS_TEMP
PROD_USER TMP$$_IX_ITEM_STATUS_ID0 PO_ITEMS_TEMP
PROD_USER TMP$$_PK_PBI_ITEM_ID0 PO_ITEMS_TEMP

--同步一次源表和中間表的資料
exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('PROD_USER','PO_ITEMS','PO_ITEMS_TEMP')

--完成聯機重定義轉換
exec dbms_redefinition.finish_redef_table('PROD_USER','PO_ITEMS','PO_ITEMS_TEMP')
--這一步完成後，所有依賴物件的名稱也與中間表的名稱交換，變為原來的名稱，如：
SYS.ALLOC1CN>/

OWNER INDEX_NAME TABLE_NAME
------------- ------------------------------ ------------------------------
PROD_USER IX_REQUEST_ID PO_ITEMS
PROD_USER IX_SOURCE_ID PO_ITEMS
PROD_USER IX_ITEM_STATUS_ID PO_ITEMS
PROD_USER PK_PBI_ITEM_ID PO_ITEMS
PROD_USER I_PO_ITEMS_VENDOR_DATE PO_ITEMS
PROD_USER TMP$$_IX_REQUEST_ID0 PO_ITEMS_TEMP
PROD_USER TMP$$_IX_SOURCE_ID0 PO_ITEMS_TEMP
PROD_USER TMP$$_IX_ITEM_STATUS_ID0 PO_ITEMS_TEMP
PROD_USER TMP$$_PK_PBI_ITEM_ID0 PO_ITEMS_TEMP
PROD_USER TMP$$_I_PO_ITEMS_VE0 PO_ITEMS_TEMP

--6. gather statistics
exec DBMS_STATS.GATHER_TABLE_STATS('PROD_USER','PO_ITEMS',cascade=>true,no_invalidate=>false,granularity=>'ALL')

--7. drop inter table
刪除中間表：
drop table PO_ITEMS_TEMP purge;

聯機重定義表在10g的改進

相關文章