ORACLE将不同表改为分区表
Oracle的普通表没有办法通过修改属性的方式直接转化为分区表，必须通过重建的方式进行转变，下面介绍三种效率比较高的方法，并说明它们各自的特点。
方法一：利用原表重建分区表。
步骤：
SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
表已创建。
SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;
已创建6264行。
SQL> COMMIT;
提交完成。
SQL> CREATE TABLE T_NEW (ID, TIME) 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))
6 AS SELECT ID, TIME FROM T;
表已创建。
SQL> RENAME T TO T_OLD;
表已重命名。
SQL> RENAME T_NEW TO T;
表已重命名。
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
6264
SQL> SELECT COUNT(*) FROM T PARTITION (P1);
COUNT(*)
----------
0
SQL> SELECT COUNT(*) FROM T PARTITION (P2);
COUNT(*)
----------
6246
SQL> SELECT COUNT(*) FROM T PARTITION (P3);
COUNT(*)
----------
18
优点：方法简单易用，由于采用DDL语句，不会产生UNDO，且只产生少量REDO，效率相对较高，而且建表完成后数据已经在分布到各个分区中了。
不足：对于数据的一致性方面还需要额外的考虑。由于几乎没有办法通过手工锁定T表的方式保证一致性，在执行CREATE TABLE语句和RENAME T_NEW TO T语句直接的修改可能会丢失，如果要保证一致性，需要在执行完语句后对数据进行检查，而这个代价是比较大的。另外在执行两个RENAME语句之间执行的对T的访问会失败。
适用于修改不频繁的表，在闲时进行操作，表的数据量不宜太大。
方法二：使用交换分区的方法。
步骤：
SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE);
表已创建。
SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS;
已创建6264行。
SQL> COMMIT;
提交完成。
SQL> CREATE TABLE T_NEW (ID NUMBER PRIMARY KEY, TIME DATE) PARTITION BY RANGE (TIME)
2 (PARTITION P1 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')),
3 PARTITION P2 VALUES LESS THAN (MAXVALUE));
表已创建。
SQL> ALTER TABLE T_NEW EXCHANGE PARTITION P1 WITH TABLE T;
表已更改。
SQL> RENAME T TO T_OLD;
表已重命名。
SQL> RENAME T_NEW TO T;
表已重命名。
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
6264
优点：只是对数据字典中分区和表的定义进行了修改，没有数据的修改或复制，效率最高。如果对数据在分区中的分布没有进一步要求的话，实现比较简单。在执行完RENAME操作后，可以检查T_OLD中是否存在数据，如果存在的话，直接将这些数据插入到T中，可以保证对T插入的操作不会丢失。
不足：仍然存在一致性问题，交换分区之后RENAME T_NEW TO T之前，查询、更新和删除会出现错误或访问不到数据。如果要求数据分布到多个分区中，则需要进行分区的SPLIT操作，会增加操作的复杂度，效率也会降低。
适用于包含大数据量的表转到分区表中的一个分区的操作。应尽量在闲时进行操作。
方法三：Oracle9i以上版本，利用在线重定义功能
步骤：
1.首先建立测试表，并插入测试数据：
SQL> conn stream/stream
Connected.
SQL> create table t_test(id number,code varchar2(5),identifier varchar2(20));
Table created.
SQL> insert into t_test values(1,'01','01-01-0001-000001');
1 row created.
SQL> insert into t_test values(2,'02','02-01-0001-000001');
1 row created.
SQL> insert into t_test values(3,'03','03-01-0001-000001');
1 row created.
SQL> insert into t_test values(4,'04','04-01-0001-000001');
1 row created.
SQL> commit;
Commit complete.
SQL> alter table t_test add constraint pk_test_id primary key (id);
Table altered
2.检查下这张表是否可以在线重定义，无报错表示可以，报错会给出错误信息：
SQL> exec dbms_redefinition.can_redef_table('stream', 't_test');
PL/SQL procedure successfully completed
3.建立在线重定义需要的中间表，表结构就是要将原测试表重定义成什么样子，这里建立的是按全宗号分区的分区表：
SQL> create table t_temp(id number,code varchar2(5),
2 identifier varchar2(20)) partition by list (code)
3 (partition part01 values('01'),
4 partition part02 values('02'),
5 partition part03 values('03'),
6 partition part04 values('04'),
7 partition part05 values(default)
8 );
Table created
SQL> alter table t_temp add constraint pk_temp_id primary key (id);
Table altered
4.启动在线重定义：
SQL> exec dbms_redefinition.start_redef_table('stream', 't_test', 't_temp');
PL/SQL procedure successfully completed
这里dbms_redefinition包的start_redef_table模块有3个参数，分别是SCHEMA名字、原表的名字、中间表的名字。
5.启动在线重定义后，中间表就可以查到原表的数据。
SQL> select * from t_temp;
ID CODE IDENTIFIER
---------- ----- --------------------
1 01 01-01-0001-000001
2 02 02-01-0001-000001
3 03 03-01-0001-000001
4 04 04-01-0001-000001
6.由于在生成系统中，在线重定义的过程中原数据表可能会发生数据改变，向原表中插入数据模拟数据改变。
SQL> insert into t_test values(5,'05','05-01-0001-000001');
1 row inserted
SQL> commit;
Commit complete
7.此时原表被修改，中间表并没有更新。
SQL> select * from t_test;
ID CODE IDENTIFIER
---------- ----- --------------------
5 05 05-01-0001-000001
1 01 01-01-0001-000001
2 02 02-01-0001-000001
3 03 03-01-0001-000001
4 04 04-01-0001-000001
SQL> select * from t_temp;
ID CODE IDENTIFIER
---------- ----- --------------------
1 01 01-01-0001-000001
2 02 02-01-0001-000001
3 03 03-01-0001-000001
4 04 04-01-0001-000001
8.使用dbms_redefinition包的sync_interim_table模块刷新数据后，中间表也可以看到数据更改。
SQL> exec dbms_redefinition.sync_interim_table('stream', 't_test', 't_temp');
PL/SQL procedure successfully completed
SQL> select * from t_test;
ID CODE IDENTIFIER
---------- ----- --------------------
5 05 05-01-0001-000001
1 01 01-01-0001-000001
2 02 02-01-0001-000001
3 03 03-01-0001-000001
4 04 04-01-0001-000001
SQL> select * from t_temp;
ID CODE IDENTIFIER
---------- ----- --------------------
1 01 01-01-0001-000001
2 02 02-01-0001-000001
3 03 03-01-0001-000001
4 04 04-01-0001-000001
5 05 05-01-0001-000001
9.结束在线重定义
SQL> exec dbms_redefinition.finish_redef_table('stream', 't_test', 't_temp');
PL/SQL procedure successfully completed
10.验证数据
SQL> select * from t_test;
ID CODE IDENTIFIER
---------- ----- --------------------
1 01 01-01-0001-000001
2 02 02-01-0001-000001
3 03 03-01-0001-000001
4 04 04-01-0001-000001
5 05 05-01-0001-000001
SQL> select * from t_temp;
ID CODE IDENTIFIER
---------- ----- --------------------
5 05 05-01-0001-000001
1 01 01-01-0001-000001
2 02 02-01-0001-000001
3 03 03-01-0001-000001
4 04 04-01-0001-000001
11.查看各分区数据是否正确
SQL> select table_name, partition_name from user_tab_partitions where
table_name = 'T_TEST';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
T_TEST PART01
T_TEST PART02
T_TEST PART03
T_TEST PART04
T_TEST PART05
SQL> select * from t_test partition(part01);
ID CODE IDENTIFIER
---------- ----- --------------------
1 01 01-01-0001-000001
SQL> select * from t_test partition(part02);
ID CODE IDENTIFIER
---------- ----- --------------------
2 02 02-01-0001-000001
SQL> select * from t_test partition(part03);
ID CODE IDENTIFIER
---------- ----- --------------------
3 03 03-01-0001-000001
SQL> select * from t_test partition(part04);
ID CODE IDENTIFIER
---------- ----- --------------------
4 04 04-01-0001-000001
SQL> select * from t_test partition(part05);
ID CODE IDENTIFIER
---------- ----- --------------------
5 05 05-01-0001-000001
12.在线重定义后，中间表已经没有意义，删掉中间表
SQL> drop table t_temp purge;
Table dropped
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25954236/viewspace-1625648/ ，如需转载，请注明出处，否则将追究法律责任。
