合 Oracle中如何将一个普通表转换为分区表
前言部分
导读和注意事项
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：
① 将一个普通表转换为分区表的常用方法（重点）
② 在线重定义的使用
③ ctas和insert的优化
④ DML语句如何开启并行操作，如何查看DML是否开启了并行
相关参考文章链接
参考文档都是MOS上How to Partition a Non-partitioned / Regular / Normal Table (文档 ID 1070693.6)，已上传到云盘，大家可自行下载。
本文简介
本文介绍了4种非分区表转换为分区表的几种方法，参考文档来自于MOS。
将普通表转换成分区表有4种方法，这个在MOS文档上有说明（How to Partition a Non-partitioned / Regular / Normal Table (文档 ID 1070693.6)）：
1. Export/import method
2. Insert with a subquery method
3. Partition exchange method
4. DBMS_REDEFINITION
非分区表转换为分区表的4种方法
Export/import method
采用逻辑导出导入很简单，首先在源库建立分区表，然后将数据导出，然后导入到新建的分区表即可，
1) 导出表：exp usr/pswd tables=numbers file=exp.dmp
2) 删除表：drop table numbers;
3) 重建分区表的定义：
create table numbers (qty number(3), name varchar2(15))
partition by range (qty)
(partition p1 values less than (501),
partition p2 values less than (maxvalue));
- 利用ignore=y来导入分区表：imp usr/pswd file=exp.dmp ignore=y
示例
创建普通表并插入测试数据
LHR@dlhr> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
Table created.
LHR@dlhr> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;
87069 rows created.
LHR@dlhr> commit;
Commit complete.
LHR@dlhr> select to_char(t.time, 'YYYYMM'), COUNT(1)
2 from t
3 group by to_char(t.time, 'YYYYMM');
TO_CHA COUNT(1)
------ ----------
201310 85984
201605 1107
采用expdp导出表
ZFXDESKDB2:oracle:/tmp>expdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=lhr_t.dmp INCLUDE=TABLE:\"IN \(\'T\'\)\" SCHEMAS=LHR LOGFILE=expdp_T.log
Export: Release 11.2.0.4.0 - Production on Fri May 27 11:07:46 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=lhr_t.dmp INCLUDE=TABLE:"IN ('T')" SCHEMAS=LHR LOGFILE=expdp_T.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2 MB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "LHR"."T" 1.406 MB 87091 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/oracle/app/oracle/admin/dlhr/dpdump/lhr_t.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri May 27 11:07:57 2016 elapsed 0 00:00:11
删除原表，创建一个分区表结构：
LHR@dlhr> drop table t;
Table dropped.
LHR@dlhr> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE )
2 PARTITION BY RANGE (TIME)
3 (PARTITION T1 VALUES LESS THAN (TO_DATE('201311', 'YYYYMM')),
4 PARTITION T2 VALUES LESS THAN (TO_DATE('201606', 'YYYYMM')),
5 PARTITION T3 VALUES LESS THAN (MAXVALUE))
6 ;
Table created.
LHR@dlhr>
导入到分区表
ZFXDESKDB2:oracle:/tmp>impdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=lhr_t.dmp SCHEMAS=LHR table_exists_action=APPEND LOGFILE=impdp_T.log
Import: Release 11.2.0.4.0 - Production on Fri May 27 11:12:40 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=lhr_t.dmp SCHEMAS=LHR table_exists_action=APPEND LOGFILE=impdp_T.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "LHR"."T" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "LHR"."T" 1.406 MB 87091 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at Fri May 27 11:12:46 2016 elapsed 0 00:00:05
查询导入后的情况：
SYS@dlhr> select to_char(t.time, 'YYYYMM'), COUNT(1)
2 from t
3 group by to_char(t.time, 'YYYYMM');
TO_CHA COUNT(1)
------ ----------
201310 85984
201605 1083
SYS@dlhr> SELECT D.TABLE_OWNER,D.TABLE_NAME,D.PARTITION_NAME FROM DBA_TAB_PARTITIONS d WHERE d.table_name='T';
TABLE_OWNER TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
LHR T T1
LHR T T2
LHR T T3
SYS@dlhr>
利用原表重建分区表(插入)
这种方法的特点是:
优点：方法简单易用，由于采用DDL语句，不会产生UNDO，且只产生少量REDO，效率相对较高，而且建表完成后数据已经在分布到各个分区中了。
不足：对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性，在执行CREATE TABLE语句和RENAME T_NEW TO T语句直接的修改可能会丢失，如果要保证一致性，需要在执行完语句后对数据进行检查，而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。
适用于修改不频繁的表，在闲时进行操作，表的数据量不宜太大。
主要有2种方式，ctas和insert方式，下边分别介绍：
例一：CTAS+RENAME
利用CTAS语法在创建分区表的时候可以一起插入数据，也可以创建好表结构再insert 进去。 CTAS这种方法采用DDL语句，不产生UNDO，只产生少量REDO，建表完成后数据已经在分布到各个分区中。
创建普通表并插入测试数据
LHR@dlhr> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
Table created.
LHR@dlhr> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;
87069 rows created.
LHR@dlhr> commit;
Commit complete.
LHR@dlhr> select to_char(t.time, 'YYYYMM'), COUNT(1)
2 from t
3 group by to_char(t.time, 'YYYYMM');
TO_CHA COUNT(1)
------ ----------
201310 85984
201605 1085
创建一个分区表，注意这里的分区表的列后边没有数据类型：
LHR@dlhr> CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME)
2 (PARTITION T1 VALUES LESS THAN (TO_DATE('201311', 'YYYYMM')),
3 PARTITION T2 VALUES LESS THAN (TO_DATE('201606', 'YYYYMM')),
4 PARTITION T3 VALUES LESS THAN (MAXVALUE))
5 AS SELECT ID, TIME FROM T;
Table created.
LHR@dlhr>
