dbms_redefinition利用在线重定义把普通表转化为分区表的一些测试

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

Table created.

SQL> INSERT INTO T SELECT ROWNUM,CREATED FROM ALL_OBJECTS;

13606 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from t;

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

SQL>  EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('XYS','T', DBMS_REDEFINITION.CONS_USE_PK);
BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE('XYS','T', DBMS_REDEFINITION.CONS_USE_PK); END;

*
ERROR at line 1:
ORA-12089: cannot online redefine table "XYS"."T" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 139
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1782
ORA-06512: at line 1

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

Table altered.

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

PL/SQL procedure successfully completed.

SQL> select to_char(min(time),'YYYY-MM-DD HH24:MI:SS') from t;

TO_CHAR(MIN(TIME),'
-------------------
2014-10-10 11:46:27

SQL> select to_char(max(time),'YYYY-MM-DD HH24:MI:SS') from t;

TO_CHAR(MAX(TIME),'
-------------------
2015-09-28 10:35:34

SQL> CREATE TABLE T_NEW(
  2  ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
  3  (
  4  PARTITION T_2013 VALUES LESS THAN (TO_DATE('2014-1-1', 'YYYY-MM-DD'))，
  5  PARTITION T_2014 VALUES LESS THAN (TO_DATE('2015-1-1', 'YYYY-MM-DD'))
  6  );

Table created.

SQL> exec dbms_redefinition.start_redef_table('XYS','T','T_NEW');
BEGIN dbms_redefinition.start_redef_table('XYS','T','T_NEW'); END;

*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-14400: inserted partition key does not map to any partition
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-06512: at line 1

上面的提示很显然是分区表建立时有值在分区表之外，未被包含，可以查询下数据把缺失的分区表建立起来

SQL> ALTER TABLE T_NEW ADD PARTITION T_2015 VALUES LESS THAN (TO_DATE('2016-1-1', 'YYYY-MM-DD'))
  2  ;

Table altered.

SQL>
SQL> select to_char(min(time),'YYYY-MM-DD HH24:MI:SS') from t;

TO_CHAR(MIN(TIME),'
-------------------
2014-10-10 11:46:27

SQL> select to_char(max(time),'YYYY-MM-DD HH24:MI:SS') from t;

TO_CHAR(MAX(TIME),'
-------------------
2015-09-28 10:35:34

SQL> select partition_name from user_tab_partitions where table_name='T_NEW';

PARTITION_NAME
------------------------------
T_2013
T_2014
T_2015

SQL> exec dbms_redefinition.start_redef_table('XYS','T','T_NEW');
BEGIN dbms_redefinition.start_redef_table('XYS','T','T_NEW'); END;

*
ERROR at line 1:
ORA-23539: table "XYS"."T" currently being redefined
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-06512: at line 1

SQL> select mview_name from user_mviews;

MVIEW_NAME
------------------------------
T_NEW

SQL> drop materialized view log on T;

Materialized view log dropped.

SQL> select mview_name from user_mviews;
 
no rows selected

--当执行start_redef_table的时候提示：currently being redefined，
这个时候需要做2个动作：
1.drop和表有关的实体化试图，因为在做start_redef_table的时候会生成一个实体化试图。
2.在下面说明了***
SQL> exec dbms_redefinition.start_redef_table('XYS','T','T_NEW');
BEGIN dbms_redefinition.start_redef_table('XYS','T','T_NEW'); END;

*
ERROR at line 1:
ORA-23539: table "XYS"."T" currently being redefined
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-06512: at line 1

SQL>  exec dbms_redefinition.sync_interim_table('XYS','T','T_NEW');
BEGIN dbms_redefinition.sync_interim_table('XYS','T','T_NEW'); END;

*
ERROR at line 1:
ORA-23540: Redefinition not defined or initiated
ORA-06512: at "SYS.DBMS_REDEFINITION", line 119
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1752
ORA-06512: at line 1

SQL> select count(*) from t;

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

SQL> select count(*) from t_new;

  COUNT(*)
----------
         0
第二个
***如果drop了实体化试图之后还提示错误(ORA-23539: table "XYS"."T" currently being redefined)，那么执行一下：abort_redef_table
之后再次执行start_redef_table.
SQL> exec DBMS_REDEFINITION.abort_redef_table('xys', 't', 't_new')

PL/SQL procedure successfully completed.

SQL> exec dbms_redefinition.start_redef_table('XYS','T','T_NEW');

PL/SQL procedure successfully completed.

SQL>  exec dbms_redefinition.sync_interim_table('XYS','T','T_NEW');

PL/SQL procedure successfully completed.

SQL> select count(*) from t;

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

SQL> select count(*) from t_new;

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

SQL> select * from t where rownum<=2;

        ID TIME
---------- -------------------
      1288 2014/10/10 11:46:50
      1289 2014/10/10 11:46:50

SQL> insert into t values(100000,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from t;

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

SQL> select count(*) from t_new;

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

SQL>  exec dbms_redefinition.sync_interim_table('XYS','T','T_NEW');

PL/SQL procedure successfully completed.

SQL> select count(*) from t_new;

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

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

PL/SQL procedure successfully completed.

SQL> select count(*) from t_new;

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

SQL> SELECT partitioned FROM user_tables WHERE table_name = 'T_NEW';

PAR
---
NO

SQL> SELECT partitioned FROM user_tables WHERE table_name = 'T';

PAR
---
YES

SQL> SELECT partition_name FROM user_tab_partitions WHERE table_name = 'T'

PARTITION_NAME
------------------------------
T_2013
T_2014
T_2015

SQL>

至此重定义完成，将T转变成了分区表。
 
在线重定义的一些约束：
1.需要有足够的空间来存放该表的2份数据。
2.不能变更主键的字段
3.表必须有主键
4.重定义只能在相同的schema中
5.在重定义完成之前增加新列不能指定NOT NULL
6.表不能包含LONG、BFILE、或用户定义类型的字段。
7.簇表不能重定义
8.SYS、SYSTEM的表不能重定义
9.表上有物化视图日志，或表上有定义物化视图，不能重定义
10.Horizontal sub setting of data cannot be performed during the redefinition
--=======================
和在线重定义有关的试图有下面2张：
select * from dba_redefinition_errors
select * from dba_redefinition_objects

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19602/viewspace-1810678/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19602/viewspace-1810678/