Oracle的在线重定义(将普通表转为分区表ora9i以上版本）

Oracle的在线重定义(将普通表转为分区表ora9i以上版本）

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
 ORA - 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));
 这里值得注意的一个问题是：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 ' )),
     PARTITION P20070401  VALUES  LESS THAN (TO_DATE( ' 2005-4-1 ' ,  ' YYYY-MM-DD ' )), 
     PARTITION P20070501  VALUES  LESS THAN (TO_DATE( ' 2005-5-1 ' ,  ' YYYY-MM-DD ' )),  
     PARTITION P20070601  VALUES  LESS THAN (TO_DATE( ' 2005-6-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   COUNT ( * )  FROM  T;
   COUNT ( * )
-- --------
       6264
SQL >   SELECT   COUNT ( * )  FROM  T PARTITION (P2);
   COUNT ( * )
-- --------
       6246
SQL >   SELECT   COUNT ( * )  FROM  T PARTITION (P3);


   COUNT ( * )
-- --------

至此普通表转为分区操作完成

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   < tablename > ;
drop  materialized  view   log   on  t;
    OR drop materialized t;