Oracle 9i中表的在线重定义

Oracle 9i中表的在线重定义

今天遇到要把数据库中的某张表改成分区表，而且该表在别的地方还有其他的注册信息，如果自己手工建一个分区表的替代该表的话，那就得要手工地去执行该表在其他地方的注册，所以不想删除该表再手工创建同名的分区表。想到了Oracle 9i中可以使用在线重定义的功能，就用了该功能来实现。

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确认一下。分区表上还没有建索引。。。还有很多要做。