【oracle】联机重定义 非分区表到分区表的转换

Posted on 2012-04-20 20:06 雅飞士 阅读(11) 评论(0) 编辑 收藏

联机重定义 非分区表到分区表的转换

数据库版本：
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
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;
         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';
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');
TABLE_NAME                     PARTITIONED
------------------------------ -----------
TESTCOL                        YES
TESTCOL_TMP                    NO

分区中有了数据：
SQL> select * from testcol partition(test_part3);
         A B                    D
---------- -------------------- --------------------
        21 1                    1