Oracle普通表修改為分割槽表的方法

資料庫版本是9.2.0.6
需要將幾個1T左右的普通表變更為分割槽表.

嘗試過下面三種方法:
1.先建立空的分割槽表,然後將原表中的資料insert進去;
2.使用Oracle的線上重定義工具
3.CTAS方法.

執行辦法,比較:
第一種方法：先建立空的分割槽表,然後將原表中的資料insert進去
SQL> create table t as select object_id,object_name from all_objects;

Table created.

create table t_new
(OBJECT_ID NUMBER,
OBJECT_NAME VARCHAR2(30))
partition by range(object_id)
(partition P1 values less than (100),
partition P2 values less than (1000),
partition P3 values less than (10000),
partition P4 values less than (MAXVALUE));

然後insert into t_new select * from t;

rename t to t_old;
rename t_new to t;
然後將原來t上的索引在新的t表上新建,完成.

這個方法比較麻煩，也是最簡單易學的一種操作方法,對於小表,而且是靜止狀態下的表,可以進行.

第二種方法：使用Oracle的線上重定義工具
Oracle提供的線上重定義的工具無疑對於線上執行的表的重建有很重要的意義.他可以在表線上的情況下重定義表的結構.
如果需要重定義的表上面有主鍵，則可以直接使用主鍵進行重定義，如果表上沒有主鍵,則可以使用rowid的方式重新定義.

我們來演示一下使用rowid重定義表的方法
首先驗證是否可以進行線上重定義
SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('CHARSI','T',dbms_redefinition.cons_use_rowid);

PL/SQL procedure successfully completed.

驗證我們的測試表和資料
SQL> select count(1) from t;

COUNT(1)
----------
49226

SQL> select count(1) from t_new;

COUNT(1)
----------
0

SQL>

SQL> select owner,segment_name,partition_name from dba_segments where owner='CHARSI' and segment_name like 'T%';

OWNER SEGMENT_NAME PARTITION_NAME
------------------------------ -------------------------------------------------- ------------------------------
CHARSI T_NEW P3
CHARSI T_NEW P2
CHARSI T
CHARSI T_NEW P1
CHARSI T_NEW P4

開始線上重定義：

SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE('CHARSI','T','T_NEW',OPTIONS_FLAG=>dbms_redefinition.cons_use_rowid);

PL/SQL procedure successfully completed.

SQL> select count(1) from t;

COUNT(1)
----------
49226

SQL> select count(1) from t_new;

COUNT(1)
----------
49226

驗證同步的情況：（可以看到，因為我們沒有執行同步，所以T表有更新的話，新表的資料並沒有實時更新）
SQL> insert into t select * from t where rownum <11;

10 rows created.

SQL> commit;

Commit complete.

SQL> select count(1) from t;

COUNT(1)
----------
49236

SQL> select count(1) from t_new;

COUNT(1)
----------
49226

我們手工做一次資料同步：（可以看到手工做同步之後資料又同步過去了）
SQL> exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('CHARSI','T','T_NEW');

PL/SQL procedure successfully completed.

SQL> select count(1) from t;

COUNT(1)
----------
49236

SQL> select count(1) from t_new;

COUNT(1)
----------
49236

結束資料同步：
SQL> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('CHARSI','T','T_NEW');

PL/SQL procedure successfully completed.

線上重定義的方法原理是首先將原表的資料和新表的資料做一次同步,之後每執行一次SYNC_INTERIM_TABLE就會將原表更新的資料同步到新表中,知道執行結束同步命令FINISH_REDEF_TABLE.
但是有一個問題是,如果我需要做的表有1T，那麼由於快照過期的問題或者undo表空間不夠的原因,根本不能完成第一次同步.
會報下面的錯誤：
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO'
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 146
ORA-06512: at line 1

第三種方法：使用create table as select 的方法
這種方法在使用得當的時候很好用,因為相比先建立表再insert，它基本很少會使用undo表空間和臨時表空間,而且速度也比較快.
如果表比較大（我測試用的表有1T以上）,而表中的資料不會實時更新,則推薦使用這種方法.

create table t_old
partition by range(object_id)
(partition P1 values less than (100),
partition P2 values less than (1000),
partition P3 values less than (10000),
partition P4 values less than (MAXVALUE))
as select * from t;

[@more@]

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