原 Oracle中如何将一个普通表转换为分区表
👉 本文共约5554个字，系统预计阅读时间或需21分钟。
目录
前言部分
导读和注意事项
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~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)）：
Export/import method
Insert with a subquery method
Partition exchange method
DBMS_REDEFINITION
非分区表转换为分区表的4种方法
导出/导入方法（Export/Import Method）
采用逻辑导出导入很简单，首先在源库建立分区表，然后将数据导出，然后导入到新建的分区表即可，
1) 导出表：exp usr/pswd tables=numbers file=exp.dmp
2) 删除表：drop table numbers;
3) 重建分区表的定义：
1 2 3 4 | CREATE TABLE T_TEST_LHR(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
示例
创建普通表并插入测试数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | 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导出表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | [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 |
删除原表，创建一个分区表结构：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | 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> |
导入到分区表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 | [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 [ZFXDESKDB2:oracle]:/tmp> 查询导入后的情况： 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，建表完成后数据已经在分布到各个分区中。
创建普通表并插入测试数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | 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 |
创建一个分区表，注意这里的分区表的列后边没有数据类型：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | 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> 改变表名 Table renamed. LHR@dlhr> rename t_new to t; Table renamed. 验证新表数据 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> |
ctas性能提升
对于CTAS建表语句性能的提升可以通过如下的方式，① 加nologging ② 并行DDL ③ 查询并行，需要说明的是建表完成后根据需要将表修改为logging模式。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 | CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME) (PARTITION T1 VALUES LESS THAN (TO_DATE('201311', 'YYYYMM')), PARTITION T2 VALUES LESS THAN (TO_DATE('201606', 'YYYYMM')), PARTITION T3 VALUES LESS THAN (MAXVALUE)) nologging parallel 4 AS SELECT /*+PARALLEL*/ ID, TIME FROM T; 执行计划： SYS@dlhr> explain plan for CREATE TABLE T_NEW (ID, TIME) 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 nologging parallel 4 7 AS SELECT /*+PARALLEL*/ ID, TIME FROM T; Explained. SYS@dlhr> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 4064487821 ---------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ---------------------------------------------------------------------------------------------------------------- | 0 | CREATE TABLE STATEMENT | | 82787 | 1778K| 14 (0)| 00:00:01 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 82787 | 1778K| 4 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) | | 3 | LOAD AS SELECT | T_NEW | | | | | Q1,00 | PCWP | | | 4 | PX BLOCK ITERATOR | | 82787 | 1778K| 4 (0)| 00:00:01 | Q1,00 | PCWC | | | 5 | TABLE ACCESS FULL | T | 82787 | 1778K| 4 (0)| 00:00:01 | Q1,00 | PCWP | | ---------------------------------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) - automatic DOP: skipped because of IO calibrate statistics are missing 17 rows selected. SYS@dlhr> |
可以看到对T表的查询是并行的，create table也是并行的，这在源表的数据量非常大的情况下性能显著。
例二: Insert with a subquery method
这种方法就是先建立表结构然后使用insert 来实现。
看示例：
创建普通表T_LHR_20160527
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 | LHR@dlhr> CREATE TABLE T_LHR_20160527 (ID NUMBER PRIMARY KEY, TIME DATE); Table created. LHR@dlhr> INSERT INTO T_LHR_20160527 SELECT ROWNUM, CREATED FROM DBA_OBJECTS; 87098 rows created. LHR@dlhr> commit; Commit complete. LHR@dlhr> select to_char(t.time, 'YYYYMM'), COUNT(1) 2 from T_LHR_20160527 t 3 group by to_char(t.time, 'YYYYMM'); TO_CHA COUNT(1) ------ ---------- 201310 85984 201605 1114 创建一个分区表T_LHR_20160527_NEW： LHR@dlhr> CREATE TABLE T_LHR_20160527_NEW (ID NUMBER, 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)); Table created. 从源表查询插入到新表中： LHR@dlhr> alter table T_LHR_20160527_NEW nologging; Table altered. LHR@dlhr> alter session enable parallel dml; Session altered. LHR@dlhr> insert /*+APPEND PARALLEL*/ into T_LHR_20160527_NEW (ID, TIME) select * from T_LHR_20160527; 87098 rows created. LHR@dlhr> commit; Commit complete. 删除源表，重命名新表 LHR@dlhr> drop table T_LHR_20160527; Table dropped. LHR@dlhr> rename T_LHR_20160527_NEW to T_LHR_20160527; Table renamed. 验证新表数据： LHR@dlhr> select to_char(t.time, 'YYYYMM'), COUNT(1) 2 from T_LHR_20160527 t 3 group by to_char(t.time, 'YYYYMM'); TO_CHA COUNT(1) ------ ---------- 201310 85984 201605 1114 LHR@dlhr> |
insert性能提升
INSERT性能提升的方式，① 表修改为nologging ② 禁用表上的索引，可以将数据插入完成后再建索引 ③ 启用并行DML alter session enable parallel dml; ④ 采用 append方式插入
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 | commit; alter session enable parallel dml; alter table T_LHR_20160527_NEW nologging; insert /*+APPEND PARALLEL*/ into T_LHR_20160527_NEW (ID, TIME) select /*+PARALLEL(t3,4)*/ * from T_LHR_20160527; 采用并行DML必须执行alter session enable parallel dml;才可以启用并行DML,执行计划: LHR@dlhr> explain plan for insert /*+APPEND PARALLEL*/ into T_LHR_20160527 (ID, TIME) select /*+PARALLEL(t3,4)*/ * from t3; Explained. LHR@dlhr> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 584641640 --------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 6897K| 144M| 272 (4)| 00:00:04 | | | | | 1 | LOAD AS SELECT | T_LHR_20160527 | | | | | | | | | 2 | PX COORDINATOR | | | | | | | | | | 3 | PX SEND QC (RANDOM)| :TQ10000 | 6897K| 144M| 272 (4)| 00:00:04 | Q1,00 | P->S | QC (RAND) | | 4 | PX BLOCK ITERATOR | | 6897K| 144M| 272 (4)| 00:00:04 | Q1,00 | PCWC | | | 5 | TABLE ACCESS FULL| T3 | 6897K| 144M| 272 (4)| 00:00:04 | Q1,00 | PCWP | | --------------------------------------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) - automatic DOP: skipped because of IO calibrate statistics are missing 17 rows selected. LHR@dlhr> commit; Commit complete. LHR@dlhr> alter session enable parallel dml; Session altered. LHR@dlhr> explain plan for insert /*+APPEND PARALLEL*/ into T_LHR_20160527 (ID, TIME) select /*+PARALLEL(t3,4)*/ * from t3; Explained. LHR@dlhr> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 576433284 --------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 6897K| 144M| 272 (4)| 00:00:04 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10000 | 6897K| 144M| 272 (4)| 00:00:04 | Q1,00 | P->S | QC (RAND) | | 3 | LOAD AS SELECT | T_LHR_20160527 | | | | | Q1,00 | PCWP | | | 4 | PX BLOCK ITERATOR | | 6897K| 144M| 272 (4)| 00:00:04 | Q1,00 | PCWC | | | 5 | TABLE ACCESS FULL| T3 | 6897K| 144M| 272 (4)| 00:00:04 | Q1,00 | PCWP | | --------------------------------------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) - automatic DOP: skipped because of IO calibrate statistics are missing 17 rows selected. LHR@dlhr> |
使用交换分区的方法(Partition exchange method)
这种方法的特点
优点：只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。如果对数据在分区中的分布没有进一步要求的话，实现比较简单。在执行完RENAME操作后，可以检查T_OLD中是否存在数据，如果存在的话，直接将这些数据插入到T中，可以保证对T插入的操作不会丢失。
不足：仍然存在一致性问题，交换分区之后RENAME T_NEW TO T之前，查询、更新和删除会出现错误或访问不到数据。如果要求数据分布到多个分区中，则需要进行分区的SPLIT操作，会增加操作的复杂度，效率也会降低。
适用于包含大数据量的表转到分区表中的一个分区的操作。应尽量在闲时进行操作。
单个分区示例
举例来说明
创建普通表并插入测试数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 | LHR@dlhr> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE); Table created. LHR@dlhr> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS where CREATED<=to_date('201311','YYYYMM'); 85984 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 创建分区表 LHR@dlhr> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME) 2 (PARTITION T1 VALUES LESS THAN (TO_DATE('2013-11-1', 'YYYY-MM-DD')), 3 PARTITION T2 VALUES LESS THAN (MAXVALUE)); Table created. 交换数据 LHR@dlhr> ALTER TABLE T_NEW EXCHANGE PARTITION T1 WITH TABLE T; Table altered. 改变表名 LHR@dlhr> rename t to t_old; Table renamed. LHR@dlhr> rename t_new to t; Table renamed. 查询数据 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 |
多个分区示例
交换分区的操作步骤如下：
创建分区表，假设有2个分区，P1，P2.
创建表A存放P1规则的数据。
创建表B 存放P2规则的数据。
用表A 和P1 分区交换。 把表A的数据放到到P1分区
用表B 和p2 分区交换。 把表B的数据存放到P2分区。
MOS上的例子
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | This example creates the exchange table with the same structure as the partitions of the partitioned table p_emp. SQL> CREATE TABLE p_emp 2 (sal NUMBER(7,2)) 3 PARTITION BY RANGE(sal) 4 (partition emp_p1 VALUES LESS THAN (2000), 5 partition emp_p2 VALUES LESS THAN (4000)); Table created. SQL> SELECT * FROM emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected. SQL> CREATE TABLE exchtab1 as SELECT sal FROM emp WHERE sal<2000; Table created. SQL> CREATE TABLE exchtab2 as SELECT sal FROM emp WHERE sal BETWEEN 2000 AND 3999; Table created. SQL> alter table p_emp exchange partition emp_p1 with table exchtab1; Table altered. SQL> alter table p_emp exchange partition emp_p2 with table exchtab2; Table altered. |
利用在线重定义功能(DBMS_REDEFINITION)
优点：保证数据的一致性，在大部分时间内，表T都可以正常进行DML操作。只在切换的瞬间锁表，具有很高的可用性。这种方法具有很强的灵活性，对各种不同的需要都能满足。而且，可以在切换前进行相应的授权并建立各种约束，可以做到切换完成后不再需要任何额外的管理操作。
不足：实现上比上面两种略显复杂。
适用于各种情况。
在线重定义的大致操作流程如下：
（1）创建基础表A，如果存在，就不需要操作。
（2）创建临时的分区表B结构。
（3）开始重定义，将基表A的数据导入临时分区表B。
（4）结束重定义，完成后在DB的 Name Directory里，已经将2个表进行了交换。即此时基表A成了分区表，我们创建的临时分区表B 成了普通表。 此时我们可以删除我们创建的临时表B。它已经是普通表。
主要过程如下所示：
1 2 3 4 5 6 7 | LHR@DLHR> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER,'T','T_NEW','ID ID,TIME TIME',DBMS_REDEFINITION.CONS_USE_PK); PL/SQL procedure successfully completed. LHR@DLHR> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(user, 'T', 'T_NEW'); PL/SQL procedure successfully completed. |
MOS上的文档：
在线重定义的相关知识
在线重定义功能
这个功能只在9.2.0.4以后的版本才有，在线重定义表具有以下功能：
（1）修改表的存储参数；
（2）将表转移到其他表空间；
（3）增加并行查询选项；
（4）增加或删除分区；
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
