使用ORACLE線上重定義將普通表改為分割槽表

bfc99發表於2014-07-03
Oracle

以下轉自：http://jishu.zol.com.cn/4032.html 作者：匿名

       客戶有張大表，在設計的時候是分割槽表，按全宗號分了77個分割槽，最近發現對這張表查詢速度明顯比之前慢了許多，經過分析發現這張表的分割槽不見啦，變成了普通表，問了看法人員才知道，原來他對這張表做了好多次ALTER TABLE XX RENAME和CREATE TABLE XX AS SELECT *操作，由於CREATE TABLE AS（CTAS）操作只會建立同樣的表結構而不會建立分割槽，導致這張表由分割槽表變成了普通的堆表，那麼就要將這張表再改回分割槽表，普通表改為分割槽表的方法很多，但是對於7*24的系統來說，就只能用ORACLE 10g版本推出的新功能-線上重定義了，下面的案例是在虛擬機器上做的測試：

本文環境：OEL 5.4 for 32bit，ORACLE 10.2.0.1.0 for 32bit
1.首先建立測試表，並插入測試資料： 

SQL> conn stream/stream
Connected.
SQL> create table t_test(id number,code varchar2(5),identifier varchar2(20));
Table created.
SQL> insert into t_test values(1,'01','01-01-0001-000001');  
1 row created.
SQL> insert into t_test values(2,'02','02-01-0001-000001');
1 row created.
SQL> insert into t_test values(3,'03','03-01-0001-000001');
1 row created.
SQL> insert into t_test values(4,'04','04-01-0001-000001');
1 row created.
SQL> commit;
Commit complete.
SQL> alter table t_test add constraint pk_test_id primary key (id);
Table altered

　　2.檢查下這張表是否可以線上重定義，無報錯表示可以，報錯會給出錯誤資訊： 

SQL> exec dbms_redefinition.can_redef_table('stream', 't_test');
PL/SQL procedure successfully completed

　　3.建立線上重定義需要的中間表，表結構就是要將原測試表重定義成什麼樣子，這裡建立的是按全宗號分割槽的分割槽表： 

SQL> create table t_temp(id number,code varchar2(5),
  2  identifier varchar2(20)) partition by list (code)
  3  (partition part01 values('01'),
  4  partition part02 values('02'),
  5  partition part03 values('03'),
  6  partition part04 values('04'),
  7  partition part05 values(default)
  8  );
Table created
SQL> alter table t_temp add constraint pk_temp_id primary key (id);
Table altered

　　4.啟動線上重定義： 

SQL> exec dbms_redefinition.start_redef_table('stream', 't_test', 't_temp');
PL/SQL procedure successfully completed

　　這裡dbms_redefinition包的start_redef_table模組有3個引數，分別是SCHEMA名字、原表的名字、中間表的名字。
5.啟動線上重定義後，中間表就可以查到原表的資料。 

SQL> select * from t_temp;
        ID CODE  IDENTIFIER
---------- ----- --------------------
         1 01    01-01-0001-000001
         2 02    02-01-0001-000001
         3 03    03-01-0001-000001
         4 04    04-01-0001-000001

　　6.由於在生成系統中，線上重定義的過程中原資料表可能會發生資料改變，向原表中插入資料模擬資料改變。 

SQL> insert into t_test values(5,'05','05-01-0001-000001');
1 row inserted
SQL> commit;
Commit complete

　　7.此時原表被修改，中間表並沒有更新。 

SQL> select * from t_test;
        ID CODE  IDENTIFIER
---------- ----- --------------------
         5 05    05-01-0001-000001
         1 01    01-01-0001-000001
         2 02    02-01-0001-000001
         3 03    03-01-0001-000001
         4 04    04-01-0001-000001
SQL>  select * from t_temp;
        ID CODE  IDENTIFIER
---------- ----- --------------------
         1 01    01-01-0001-000001
         2 02    02-01-0001-000001
         3 03    03-01-0001-000001
         4 04    04-01-0001-000001

　　8.使用dbms_redefinition包的sync_interim_table模組重新整理資料後，中間表也可以看到資料更改。 

SQL> exec dbms_redefinition.sync_interim_table('stream', 't_test', 't_temp');
PL/SQL procedure successfully completed
SQL> select * from t_test;
        ID CODE  IDENTIFIER
---------- ----- --------------------
         5 05    05-01-0001-000001
         1 01    01-01-0001-000001
         2 02    02-01-0001-000001
         3 03    03-01-0001-000001
         4 04    04-01-0001-000001
SQL> select * from t_temp;
        ID CODE  IDENTIFIER
---------- ----- --------------------
         1 01    01-01-0001-000001
         2 02    02-01-0001-000001
         3 03    03-01-0001-000001
         4 04    04-01-0001-000001
         5 05    05-01-0001-000001

　　9.結束線上重定義 

SQL> exec dbms_redefinition.finish_redef_table('stream', 't_test', 't_temp');
PL/SQL procedure successfully completed

　　10.驗證資料 

SQL> select * from t_test;
        ID CODE  IDENTIFIER
---------- ----- --------------------
         1 01    01-01-0001-000001
         2 02    02-01-0001-000001
         3 03    03-01-0001-000001
         4 04    04-01-0001-000001
         5 05    05-01-0001-000001
SQL> select * from t_temp;
        ID CODE  IDENTIFIER
---------- ----- --------------------
         5 05    05-01-0001-000001
         1 01    01-01-0001-000001
         2 02    02-01-0001-000001
         3 03    03-01-0001-000001
         4 04    04-01-0001-000001

　　11.檢視各分割槽資料是否正確 

SQL> select table_name, partition_name from user_tab_partitions where 
table_name = 'T_TEST';
TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
T_TEST                         PART01
T_TEST                         PART02
T_TEST                         PART03
T_TEST                         PART04
T_TEST                         PART05
SQL> select * from t_test partition(part01);
        ID CODE  IDENTIFIER
---------- ----- --------------------
         1 01    01-01-0001-000001
SQL> select * from t_test partition(part02);
        ID CODE  IDENTIFIER
---------- ----- --------------------
         2 02    02-01-0001-000001
SQL> select * from t_test partition(part03);
        ID CODE  IDENTIFIER
---------- ----- --------------------
         3 03    03-01-0001-000001
SQL> select * from t_test partition(part04);
        ID CODE  IDENTIFIER
---------- ----- --------------------
         4 04    04-01-0001-000001
SQL> select * from t_test partition(part05);
        ID CODE  IDENTIFIER
---------- ----- --------------------
         5 05    05-01-0001-000001

　　12.線上重定義後，中間表已經沒有意義，刪掉中間表 

SQL> drop table t_temp purge;
Table dropped


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

相關文章