Oracle普通表轉分區表的幾種方法

將普通表轉換成分區表有4種方法： 

  1)  Export/import method 

  2)  Insert with a subquery method 

  3)  Partition exchange method(交換分區)

  4)  DBMS_REDEFINITION(在線重定義)

交換分區的步驟：

1)  創建分區表，假設有2個分區，P1，P2

2)  創建表A存放P1規則的數據

3)  創建表B 存放P2規則的數據

4)  用表A 和P1 分區交換，把表A的數據放到到P1分區

5)  用表B 和p2 分區交換，把表B的數據存放到P2分區

示例:

a. 創建分區表:

sql> create table p_dba 

  2    (id number,time date) 

  3    partition by range(time) 

  4    ( 

  5    partition p1 values less than (to_date('2010-09-1', 'yyyy-mm-dd')), 

  6    partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')) 

  7    ); 

b.創建2個分別對應分區的基表：

SQL>  CREATE  TABLE  dba_p1  as  SELECT  id,time_fee  FROM  dba_old WHERE   

time_fee<TO_DATE('2010-09-1', 'YYYY-MM-DD');

SQL>  CREATE  TABLE  dba_p2  as  SELECT  id,time_fee  FROM  dba_old WHERE   

time_fee<TO_DATE('2010-11-1',  'YYYY-MM-DD')  and 

time_fee>TO_DATE('2010-09-1', 'YYYY-MM-DD');

c.將2個基表與2個分區進行交換： 

SQL> alter table p_dba exchange partition p1 with table dba_p1; 

表已更改。 

SQL> alter table p_dba exchange partition p2 with table dba_p2; 

表已更改。 

在線重定義能保證數據的一致性，在大部分時間內，表都可以正常進行DML操作。

只在切換的瞬間鎖表，具有很高的可用性。這種方法具有很強的靈活性，對各種不同的需要都能滿足。

而且，可以在切換前進行相應的授權并建立各種約束，可以做到切換完成后不再需要任何額外的管理操作。

這個功能只在9.2.0.4以后的版本才有，在線重定義表具有以下功能：

（1）修改表的存儲參數 

（2）將表轉移到其他表空間

（3）增加并行查詢選項

（4）增加或刪除分區

（5）重建表以減少碎片 

（6）將堆表改為索引組織表或相反的操作

（7）增加或刪除一個列

使用在線重定義的一些限制條件： 

（1） There must be enough space to hold two copies of the table.  

（2） Primary key columns cannot be modified.  

（3） Tables must have primary keys.  

（4） Redefinition must be done within the same schema.  

（5） New  columns  added  cannot  be  made  NOT  NULL  until  after  the  redefinition operation.  

（6） Tables cannot contain LONGs, BFILEs or User Defined Types.  

（7） Clustered tables cannot be redefined.  

（8） Tables in the SYS or SYSTEM schema cannot be redefined.  

（9） Tables  with  materialized  view  logs  or  materialized  views  defined  on  them cannot be redefined.  

（10） Horizontal sub setting of data cannot be performed during the redefinition.  

在線重定義的大致操作流程如下： 

（1）創建基礎表A，如果存在，就不需要操作

（2）創建臨時的分區表B

（3）開始重定義，將基表A的數據導入臨時分區表B

（4）結束重定義，此時在DB的 Name Directory里，已經將2個表進行了交換。即此時基表A成了分區表，我們創建的臨時分區表B 成了普通表。此時我們可以刪除我們創建的臨時表B。

示例：

a. 創建基本表和索引

sql> create table unpar_table ( 

  2    id number(10) primary key, 

  3    create_date date 

  4    );

b. 收集表的統計信息 

sql> exec dbms_stats.gather_table_stats('icd', 'unpar_table', cascade => true); 

pl/sql 過程已成功完成

c. 創建臨時分區表 

sql>  create  table    par_table  (id  number  primary  key,  time  date)  partition  by  range 

(time) 

  2    (partition p1 values less than (to_date('2004-7-1', 'yyyy-mm-dd')), 

  3    partition p2 values less than (to_date('2005-1-1', 'yyyy-mm-dd')), 

  4    partition p3 values less than (to_date('2005-7-1', 'yyyy-mm-dd')), 

  5    partition p4 values less than (maxvalue));

d. 進行重定義操作

d1. 檢查重定義的合理性 

d2. 如果d1沒有問題，開始重定義，這個過程可能要等一會

這里要注意：如果分區表和原表列名相同，可以用如下方式進行： 

SQL> BEGIN  

DBMS_REDEFINITION.start_redef_table(  

uname => 'ICD',    

orig_table => 'unpar_table',  

int_table => 'par_table');  

END;  

/

如果分區表的列名和原表不一致，那么在開始重定義的時候，需要重新指定

映射關系： 

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE( 

'ICD', 

'unpar_table', 

'par_table', 

'ID ID, create_date TIME', -- 在這里指定新的映射關系 

DBMS_REDEFINITION.CONS_USE_PK); 

這一步操作結束后，數據就已經同步到這個臨時的分區表里來了。

d3. 同步新表，這是可選的操作

SQL> BEGIN 

  2    dbms_redefinition.sync_interim_table( 

  3    uname => 'ICD', 

  4    orig_table => 'unpar_table', 

  5    int_table => 'par_table'); 

  6    END; 

  7    / 

PL/SQL 過程已成功完成。

d4. 創建索引，在線重定義只重定義數據，索引還需要單獨建立

sql> create index create_date_ind2 on par_table(time); 

d5. 收集新表的統計信息

sql> exec dbms_stats.gather_table_stats('icd', 'par_table', cascade => true);

d6. 結束重定義 

SQL> BEGIN 

  2    dbms_redefinition.finish_redef_table( 

  3    uname => 'ICD', 

  4    orig_table => 'unpar_table', 

  5   int_table => 'par_table'); 

  6    END; 

  7    / 

PL/SQL 過程已成功完成

結束重定義的意義： 

  基表unpar_table  和臨時分區表par_table  進行了交換。  此時臨時分區表

par_table成了普通表，我們的基表unpar_table成了分區表。  

  我們在重定義的時候，基表unpar_table是可以進行DML操作的。 只有在2

個表進行切換的時候會有短暫的鎖表。