OCM實驗-使用線上重定義方式遷移表

要求使用線上重定義方式遷移表
優點：支援線上讀/寫，不影響大查詢，對海量資料的表進行操作效率非常好，實質只更新資料字典，不移動資料
缺點：線上重定義後表上的主鍵、索引不會同步過來，必須重建，只變換表名.在finish轉換過程中原表是鎖定狀態
官方文件： PL/SQL Packages and Types Reference -> 搜尋線上重定義dbms_redefinition

1.建立環境
SYS@testdb>create tablespace mssm datafile '/oracle/ora10g/oradata/mssm01.dbf' size 20M extent management local segment space management manual;
Tablespace created.
SYS@testdb>create tablespace assm datafile '/oracle/ora10g/oradata/assm01.dbf' size 20m extent management local segment space management auto;
Tablespace created.

SYS@testdb>select segment_space_management,tablespace_name from dba_tablespaces where tablespace_name in ('MSSM','ASSM');

SEGMEN TABLESPACE_NAME
------ ------------------------------
AUTO   ASSM
MANUAL MSSM

在mssm表空間上，建立測試表t，並插入測試資料
LEO1@testdb>create table t (id int constraint pk_t primary key) tablespace mssm;
Table created.
LEO1@testdb>insert into t values(1);
1 row created.
LEO1@testdb>insert into t values(2);
1 row created.
LEO1@testdb>insert into t values(3);
1 row created.
LEO1@testdb>commit;
Commit complete.
LEO1@testdb>select * from t;
        ID
----------
         1
         2
         3
LEO1@testdb>
LEO1@testdb>select table_name,tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T                              MSSM

2.使用線上重定義方式將t表從mssm表空間遷移到assm表空間
1）驗證是否可以基於主鍵方式遷移
LEO1@testdb>exec dbms_redefinition.can_redef_table(uname=>'leo1',tname=>'t',options_flag=>dbms_redefinition.cons_use_pk);
PL/SQL procedure successfully completed.
2）建立線上重定義中間表
LEO1@testdb>create table t_interim (id int) tablespace assm;
Table created.
LEO1@testdb>select table_name,tablespace_name from user_tables where table_name in ('T','T_INTERIM');

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T                              MSSM
T_INTERIM                      ASSM
3）線上重定義表
LEO1@testdb>exec dbms_redefinition.start_redef_table(uname=>'leo1',orig_table=>'t',int_table=>'t_interim');
PL/SQL procedure successfully completed.

LEO1@testdb>select * from t;

        ID
----------
         1
         2
         3

LEO1@testdb>select * from t_interim;

        ID
----------
         1
         2
         3
手動同步
LEO1@testdb>exec dbms_redefinition.sync_interim_table(uname=>'leo1',orig_table=>'t',int_table=>'t_interim');
PL/SQL procedure successfully completed.

完成線上重定義
******************************************************************
我在這之前做了一個drop 表的動作，證明在遷移過程中，源表是可以刪除的。
LEO1@testdb>drop table t_interim;
drop table t_interim
           *
ERROR at line 1:
ORA-12083: must use DROP MATERIALIZED VIEW to drop "LEO1"."T_INTERIM"

LEO1@testdb>drop table t;

Table dropped.

LEO1@testdb>
LEO1@testdb>select table_name from user_tables;

TABLE_NAME
------------------------------
T_INTERIM

這時，完成線上遷移將無法進行
LEO1@testdb>exec dbms_redefinition.finish_redef_table(uname=>'leo1',orig_table=>'t',int_table=>'t_interim');
BEGIN dbms_redefinition.finish_redef_table(uname=>'leo1',orig_table=>'t',int_table=>'t_interim'); END;

*
ERROR at line 1:
ORA-23540: Redefinition not defined or initiated
ORA-06512: at "SYS.DBMS_REDEFINITION", line 76
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1377
ORA-06512: at line 1
****************************************************8
重做上面的操作後，可以完成線上重定義操作了

LEO1@testdb>exec dbms_redefinition.finish_redef_table(uname=>'leo1',orig_table=>'t',int_table=>'t_interim');
PL/SQL procedure successfully completed.

檢查表所在的表空間。
LEO1@testdb>select table_name,tablespace_name from user_tables where table_name in ('T','T_INTERIM');

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T                              ASSM
T_INTERIM                      MSSM
檢查索引。
LEO1@testdb>select index_name,table_name,tablespace_name,status from user_indexes where index_name='PK_T';

INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ------------------------------ --------
PK_T                           T_INTERIM                      MSSM                           VALID

LEO1@testdb>alter index pk_t rebuild tablespace assm online;

Index altered.

LEO1@testdb>select index_name,table_name,tablespace_name,status from user_indexes where index_name='PK_T';

INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ------------------------------ --------
PK_T                           T_INTERIM                      ASSM                           VALID

額，pk_t主鍵在中間表t_interim上。因此不能使用rebuild重建

需要給t表新建主鍵。
LEO1@testdb>alter table t add constraint pk_t_id primary key(id);
Table altered.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/11590946/viewspace-1082278/，如需轉載，請註明出處，否則將追究法律責任。