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

个表进行切换的时候会有短暂的锁表。