ORACLE在线重定义

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.

如果报：

ERROR at line 1:

    ORA-06550: line 1, column 53:

    PLS-00201: identifier 'DBMS_REDEFINITION' must be declared

    ORA-06550: line 1, column 7:

    PL/SQL: Statement ignored

    出错原因是没有赋予‘DBMS_REDEFINITION' 的执行权限。解决的办法是：

    dba用户授予权限

    SQL> GRANT ALL ON SYS.DBMS_REDEFINITION TO SCOTT;

    Grant succeeded.

    SQL>  GRANT CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, LOCK ANY TABLE, SELECT ANY TABLE TO SCOTT;

    Grant succeeded.

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

CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)    
(PARTITION T_2003 VALUES LESS THAN (TO_DATE('2004-1-1', 'YYYY-MM-DD')),      
PARTITION T_2004 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')),     
PARTITION T_2005 VALUES LESS THAN (TO_DATE('2006-1-1', 'YYYY-MM-DD')),     
PARTITION T_2006 VALUES LESS THAN (TO_DATE('2007-1-1', 'YYYY-MM-DD')),     
PARTITION T_2007 VALUES LESS THAN (TO_DATE('2008-1-1', 'YYYY-MM-DD')),    
PARTITION T_2008 VALUES LESS THAN (TO_DATE('2009-1-1', 'YYYY-MM-DD')),     
PARTITION T_2009 VALUES LESS THAN (TO_DATE('2010-1-1', 'YYYY-MM-DD')),     
PARTITION T_2010 VALUES LESS THAN (TO_DATE('2011-1-1', 'YYYY-MM-DD')),     
PARTITION T_2011 VALUES LESS THAN (TO_DATE('2012-1-1', 'YYYY-MM-DD')),     
PARTITION T_2012 VALUES LESS THAN (TO_DATE('2013-1-1', 'YYYY-MM-DD')),    
 PARTITION T_2013 VALUES LESS THAN (TO_DATE('2014-1-1', 'YYYY-MM-DD')));

5、执行表的在线重定义

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'T', 'T_NEW');

6、执行把中间表的内容和数据源表进行同步。

SQL>execute dbms_redefinition.sync_interim_table(user,'t','t_new');

7、执行结束在线定义过程。

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(user, 'T', 'T_NEW');

8、查看数据字典，可以看到改表已经成为了分区表

SQL>  select a.table_name, a.partition_name from user_tab_partitions a;
T                              T_2008
T                              T_2003
T                              T_2004
T                              T_2005
T                              T_2006
T                              T_2007
T                              T_2009
T                              T_2010
T                              T_2011
T                              T_2012
T                              T_2013

SQL> SELECT COUNT(*) FROM T;
     45012
SQL> SELECT COUNT(*) FROM T PARTITION (T_2011);   
         0
SQL> SELECT COUNT(*) FROM T PARTITION (T_2012);
         0
SQL>  SELECT COUNT(*) FROM T PARTITION (T_2013);
     45012
SQL> 

9、如果执行在线重定义的过程中出错，可以在执行dbms_redefinition.start_redef_table之后到执行dbms_redefinition.finish_redef_table之前的时间里执行：DBMS_REDEFINITION.abort_redef_table(user, 't', 't_new')以放弃执行在线重定义.

10、如果出现以下错误

SQL> exec dbms_redefinition.can_redef_table(user, 't');

BEGIN dbms_redefinition.can_redef_table(user, 't'); END;

*

ERROR at line 1:

ORA-12091: cannot online redefine table "user"."t" with materialized views

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

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

ORA-06512: at line 1

用这句删除materialized view 即可继续进行

drop materialized view log on ;

drop materialized view log on t;

OR

drop materialized t;