oracle联机重定义非分区表到分区表的转换
联机重定义 非分区表到分区表的转换
数据库版本：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
创建原始表和中间表：
SQL> create table testcol (a number,b varchar2(20),d varchar2(20));
Table created www.2cto.com
SQL> create table testcol_tmp (a number,b varchar2(20),d varchar2(20))
2 partition by range(a)
3 (
4 PARTITION test_part1 values less than (10),
5 PARTITION test_part2 values less than (20),
6 PARTITION test_part3 values less than (30),
7 PARTITION test_part4 values less than (40),
8 PARTITION test_part5 values less than (50)
9 );
Table created
根据上篇的经验，给原始表建立主键：
SQL> alter table testcol add constraints pk_testcol primary key (a);
Table altered
插入数据：
insert into testcol values(1,'1','1');
insert into testcol values(11,'1','1');
insert into testcol values(21,'1','1');
insert into testcol values(31,'1','1');
insert into testcol values(41,'1','1');
SQL> select * from testcol; www.2cto.com
A B D
---------- -------------------- --------------------
1 1 1
11 1 1
21 1 1
31 1 1
41 1 1
查看转换之前原始表和中间表的分区情况：
SQL> select table_name,partitioned from user_tables where table_name in('TESTCOL','TESTCOL_TMP');
TABLE_NAME PARTITIONED
------------------------------ -----------
TESTCOL NO
TESTCOL_TMP YES
检测原始表是否能够联机重定义：
SQL> exec dbms_redefinition.can_redef_table('SCOTT','TESTCOL');
PL/SQL procedure successfully completed
检测通过。
开始联机重定义：
SQL> exec dbms_redefinition.start_redef_table('SCOTT','TESTCOL','TESTCOL_TMP');
PL/SQL procedure successfully completed
这步执行完后，分区属性还没改变。
SQL> select table_name,partitioned from user_tables where table_name in('TESTCOL','TESTCOL_TMP');
TABLE_NAME PARTITIONED
------------------------------ -----------
TESTCOL NO
TESTCOL_TMP YES
主键也没同步到中间表：
SQL> select a.constraint_name,a.constraint_type from dba_constraints a where a.table_name='TESTCOL_TMP'; www.2cto.com
CONSTRAINT_NAME CONSTRAINT_TYPE
------------------------------ ---------------
但是数据已经传到中间表：
SQL> select * from testcol_tmp;
A B D
---------- -------------------- --------------------
1 1 1
11 1 1
21 1 1
31 1 1
41 1 1
sync_interim_table的目的是为了缩短finish时锁定表的时间：
SQL> exec dbms_redefinition.sync_interim_table('SCOTT','TESTCOL','TESTCOL_TMP');
PL/SQL procedure successfully completed
完成联机重定义的最后一步，这步会暂时性的对原始表进行锁定：
SQL> exec dbms_redefinition.finish_redef_table('SCOTT','TESTCOL','TESTCOL_TMP');
PL/SQL procedure successfully completed
这步执行完成后,主键交换过来了：
SQL> select a.constraint_name,a.constraint_type from dba_constraints a where a.table_name='TESTCOL_TMP';
CONSTRAINT_NAME CONSTRAINT_TYPE
------------------------------ ---------------
PK_TESTCOL P
SQL> select a.constraint_name,a.constraint_type from dba_constraints a where a.table_name='TESTCOL';
CONSTRAINT_NAME CONSTRAINT_TYPE
------------------------------ ---------------
分区属性也交换了：
SQL> select table_name,partitioned from user_tables where table_name in('TESTCOL','TESTCOL_TMP'); www.2cto.com
TABLE_NAME PARTITIONED
------------------------------ -----------
TESTCOL YES
TESTCOL_TMP NO
分区中有了数据：
SQL> select * from testcol partition(test_part3);
A B D
---------- -------------------- --------------------
21 1 1
作者 雅飞士
oracle联机重定义非分区表到分区表的转换 
