Oracle 9i中表的線上重定義（轉）

今天遇到要把資料庫中的某張表改成分割槽表，而且該表在別的地方還有其他的註冊資訊，如果自己手工建一個分割槽表的替代該表的話，那就得要手工地去執行該表在其他地方的註冊，所以不想刪除該表再手工建立同名的分割槽表。想到了Oracle 9i中可以使用線上重定義的功能，就用了該功能來實現。

[@more@]

1．首先對要線上重定義的表自行驗證，看該表是否可以重定義，如果不可以則會提示錯誤資訊。

SQL> execute dbms_redefinition.can_redef_table('ofsa','pft_party_profit_detail');

begin dbms_redefinition.can_redef_table('ofsa','pft_party_profit_detail'); end;

ORA-12089: cannot online redefine table "OFSA"."PFT_PARTY_PROFIT_DETAIL" with no primary key

ORA-06512: at "SYS.DBMS_REDEFINITION", line 8

ORA-06512: at "SYS.DBMS_REDEFINITION", line 247

ORA-06512: at line 1

SQL>

出錯了， 該表上缺少主鍵，為該表建主鍵。再執行驗證。

SQL> alter table pft_party_profit_detail add constraint pft_party_profit_detail_pk primary key(as_of_date,id_number);

Table altered

SQL> execute dbms_redefinition.can_redef_table('ofsa','pft_party_profit_detail');

PL/SQL procedure successfully completed

SQL>

2．建個和源表表結構一樣的分割槽表，作為中間表。按日期範圍分割槽，按id_number的值進行雜湊分割槽。

create table pft_party_profit_detail_bak
(
AS_OF_DATE DATE not null,
ACCOUNT_TABLE_CD NUMBER(4) not null,
PARTY_ID NUMBER(15) not null,
ID_NUMBER NUMBER(25) not null,
IDENTITY_CODE NUMBER(10) not null,
GL_ACCOUNT_ID NUMBER(14) not null,
ORG_UNIT_ID NUMBER(14) not null,
COMMON_COA_ID NUMBER(14) not null,
ISO_CURRENCY_CD VARCHAR2(15) not null,
PARTY_TYPE_CD VARCHAR2(5) not null,
PARTY_NUMBER VARCHAR2(30) not null,
ACCOUNT_NUMBER VARCHAR2(30) not null,
ACCOUNT_CATEGORY_CD NUMBER(4) not null,
ACCOUNT_GROUP_CD NUMBER(4) not null ,
PARENT_PARTY_NUMBER VARCHAR2(30),
IDENTITY_CODE_CHG NUMBER(10) ,
RECORD_COUNT NUMBER(6) ,
PRIMARY_REL NUMBER(1) ,
PRIMARY_BAL NUMBER(14,2) ,
SECONDARY_REL NUMBER(1) ,
ACCOUNT_CONTRIB NUMBER(14,2) ,
CONTRIB_AFTER_CAPITAL_CHG NUMBER(14,2) ,
ALLOCATED_EQUITY NUMBER(14,2) ,
TOTAL_TRANSACTIONS NUMBER(8) ,
CUR_NET_BOOK_BAL_C NUMBER(14,2) ,
EQUITY_CREDIT NUMBER(14,2) ,
INTEREST_CHARGE_CREDIT NUMBER(14,2) ,
INTEREST_INC_EXP NUMBER(14,2) ,
LOAN_LOSS_PROVISION NUMBER(14,2) ,
NET_FEE_INCOME NUMBER(14,2) ,
NET_INT_MARGIN NUMBER(14,2) ,
OPEN_ACCOUNT_FLG NUMBER(1) ,
ORIGINATION_DATE DATE ,
RETURN_ON_EQUITY NUMBER(11,4) ,
TOTAL_ACCOUNT_EXP NUMBER(14,2) ,
AVG_BOOK_BAL NUMBER(30,4) ,
PC_EXP NUMBER(30,4) ,
CC_EXP NUMBER(30,4) ,
CC_N_EXP NUMBER(30,4) ,
PC_N_EXP NUMBER(30,4) ,
CUST_OFFICER_EXP NUMBER(30,4) ,
CUST_EXP NUMBER(30,4) ,
ACTIVITY_EXP NUMBER(30,4) ,
CUR_BOOK_BAL NUMBER(30,4) ,
ACTIVITY_TIMES NUMBER(30) ,
EXP_PER_ACTIVITY NUMBER(30,4) ,
NO_OBJECT_ALLOC_EXP NUMBER(38,4) ,
NO_ALLOCATION_EXP NUMBER(38,4) ,
FOREIGN_CURRENCY VARCHAR2(15) ,
PRODUCT_ID NUMBER(14)
)
partition by range(as_of_date) subpartition by hash(id_number)
(
partition p_2006_2007 values less than(to_date('2007-12-31','yyyy-mm-dd')),
partition p_2008_2009 values less than(to_date('2009-12-31','yyyy-mm-dd')),
partition p_2010_2011 values less than(to_date('2011-12-31','yyyy-mm-dd')),
partition p_2012_2013 values less than(to_date('2013-12-31','yyyy-mm-dd')),
partition p_2014_2015 values less than(to_date('2015-12-31','yyyy-mm-dd')),
partition p_2016_max values less than(maxvalue)
)

3．執行表的線上重定義：

SQL>execute dbms_redefinition.start_redef_table('ofsa','pft_party_profit_detail','pft_party_profit_detail_bak');

PL/SQL procedure successfully completed

4．執行把中間表的內容和資料來源表進行同步。

SQL>execute dbms_redefinition.sync_interim_table('ofsa','pft_party_profit_detail','pft_party_profit_detail_bak9');

PL/SQL procedure successfully completed

5．執行結束線上定義過程。

SQL>execute dbms_redefinition.finish_redef_table('ofsa','pft_party_profit_detail','pft_party_profit_detail_bak9');

PL/SQL procedure successfully completed

SQL>

檢視資料字典，可以看到改表已經成為了分割槽表。

6．如果執行線上重定義的過程中出錯，可以在執行dbms_redefinition.start_redef_table之後到執行dbms_redefinition.finish_redef_table之前的時間裡執行：DBMS_REDEFINITION.abort_redef_table以放棄執行線上重定義。

執行線上重定義的過程，要求源表和中間表要在同一個schema下。上面執行的過程雖然使用了分割槽表，但是並沒有為分割槽資料指定表空間，這還要和DBA確認一下。分割槽表上還沒有建索引。。。還有很多要做。