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