oracle在线重定义出错,Oracle的在线重定义

Basic Steps for Manual Online Reorganization Commands and procedures used:

1.DBMS_REDEFINITION.CAN_REDEF_TABLE

2.CREATE TABLE …

3.DBMS_REDEFINITION.START_REDEF_TABLE

4.DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS and DBMS_REDEFINITION.CONS_ORIG_PAGRAMS

SELECT object_name,base_table_name,ddl_txt from DBA_REDEFINITION_ERRORS;

6.DBMS_REDEFINITION.SYNC_INTERIM_TABLE

7.DBMS_REDEFINITION.FINISH_REDEF_TABLE

8.DROP TABLE ... PURGE

1.创建表

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);

2、插入数据

SQL> INSERT INTO T SELECT ROWNUM, CREATED from DBA_OBJECTS;

SQL> COMMIT;

3、在线重定义的表自行验证，看该表是否可以重定义

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'T', DBMS_REDEFINITION.CONS_USE_PK);

(如果没有定义主键会提示以下错误信息)

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

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

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

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

ORA-06512: at line 1

出错了，该表上缺少主键，为该表建主键，再执行验证.

SQL> alter table t add constraint pk_t primary key(id);

Table altered.

4、建个和源表表结构一样的分区表，作为中间表。按日期范围分区

SQL> CREATE TABLE T_NEW

(ID NUMBER PRIMARY KEY, TIME DATE)

PARTITION BY RANGE (TIME)

(PARTITION P1 VALUES LESS THAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')),

PARTITION P2 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')),

PARTITION P3 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')),

PARTITION P4 VALUES LESS THAN (MAXVALUE));

SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE)

PARTITION BY RANGE (TIME)

(PARTITION P20070201 VALUES LESS THAN (TO_DATE('2007-2-1', 'YYYY-MM-DD')),

PARTITION P20070301 VALUES LESS THAN (TO_DATE('2005-3-1', 'YYYY-MM-DD')),

&nbsp