使用dbms_redefinition普通表重定义分区表遇到的问题

在使用dbms_redefinition将普通表转换为分区表的时候,若转换过程中创建中间表的DDL太长,Oracle会截取掉部分DDL，这个问题在11.2.0.2版本已经修复，以下是在11.2.0.1下测试的。

测试如下：

测试Oracle版本:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

1.创建需要重定义的表

create table TB_HXL_USER
(
  STATEDATE DATE not null,
  USERNUMBER VARCHAR2(13) not null,
  PROVCODE NUMBER not null,
  REM1 VARCHAR2(100),
  REM2 VARCHAR2(100) default 'A' not null,
  REM3 VARCHAR2(100),
  REM4 VARCHAR2(100),
  REM5 VARCHAR2(100),
  REM6 VARCHAR2(100),
  REM7 VARCHAR2(100),
  REM8 VARCHAR2(100),
  REM9 VARCHAR2(100),
  REM10 VARCHAR2(100),
  CREATE_DATE DATE not null,
  CREATE_BY NUMBER not null,
  LAST_UPDATE_DATE DATE not null,
  LAST_UPDATE_BY NUMBER not null
);

create unique index IDX_TB_HXL_USER_U1 on TB_HXL_USER (STATEDATE, USERNUMBER, PROVCODE)
;

2.创建中间表

中间表目前是33个分区

-- Create table

create table tb_hxl_user_mid
(
  STATEDATE DATE not null,
  USERNUMBER VARCHAR2(13) not null,
  PROVCODE NUMBER not null,
  REM1 VARCHAR2(100),
  REM2 VARCHAR2(100) default 'A' not null,
  REM3 VARCHAR2(100),
  REM4 VARCHAR2(100),
  REM5 VARCHAR2(100),
  REM6 VARCHAR2(100),
  REM7 VARCHAR2(100),
  REM8 VARCHAR2(100),
  REM9 VARCHAR2(100),
  REM10 VARCHAR2(100),
  CREATE_DATE DATE not null,
  CREATE_BY NUMBER not null,
  LAST_UPDATE_DATE DATE not null,
  LAST_UPDATE_BY NUMBER not null
)
PARTITION BY RANGE (STATEDATE) SUBPARTITION BY list (PROVCODE)
SUBPARTITION TEMPLATE
         (SUBPARTITION p_01 values(01),
          SUBPARTITION p_02 values(02),
          SUBPARTITION p_03 values(03),
          SUBPARTITION p_04 values(04),
          SUBPARTITION p_05 values(05),
          SUBPARTITION p_06 values(06),
          SUBPARTITION p_07 values(07),
          SUBPARTITION p_08 values(08),
          SUBPARTITION p_09 values(09),
          SUBPARTITION p_10 values(10),
          SUBPARTITION p_11 values(11),
          SUBPARTITION p_12 values(12),
          SUBPARTITION p_13 values(13),
          SUBPARTITION p_14 values(14),
          SUBPARTITION p_15 values(15),
          SUBPARTITION p_16 values(16),
          SUBPARTITION p_17 values(17),
          SUBPARTITION p_18 values(18),
          SUBPARTITION p_19 values(19),
          SUBPARTITION p_20 values(20),
          SUBPARTITION p_21 values(21),
          SUBPARTITION p_22 values(22),
          SUBPARTITION p_23 values(23),
          SUBPARTITION p_24 values(24),
          SUBPARTITION p_25 values(25),
          SUBPARTITION p_26 values(26),
          SUBPARTITION p_27 values(27),
          SUBPARTITION p_28 values(28),
          SUBPARTITION p_29 values(29),
          SUBPARTITION p_30 values(30)
         )
   (PARTITION p_20110515 VALUES LESS THAN (to_date('20110516','YYYYMMDD'))
    );

-- Create/Recreate indexes

create unique index IDX_TB_HXL_USER_MID_U1
on TB_HXL_USER_MID (STATEDATE, USERNUMBER, PROVCODE) local;

alter table TB_HXL_USER_MID add partition p_20110516 VALUES LESS THAN (to_date('20110517','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110517 VALUES LESS THAN (to_date('20110518','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110518 VALUES LESS THAN (to_date('20110519','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110519 VALUES LESS THAN (to_date('20110520','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110520 VALUES LESS THAN (to_date('20110521','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110521 VALUES LESS THAN (to_date('20110522','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110522 VALUES LESS THAN (to_date('20110523','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110523 VALUES LESS THAN (to_date('20110524','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110524 VALUES LESS THAN (to_date('20110525','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110525 VALUES LESS THAN (to_date('20110526','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110526 VALUES LESS THAN (to_date('20110527','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110527 VALUES LESS THAN (to_date('20110528','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110528 VALUES LESS THAN (to_date('20110529','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110529 VALUES LESS THAN (to_date('20110530','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110530 VALUES LESS THAN (to_date('20110531','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110531 VALUES LESS THAN (to_date('20110601','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110601 VALUES LESS THAN (to_date('20110602','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110602 VALUES LESS THAN (to_date('20110603','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110603 VALUES LESS THAN (to_date('20110604','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110604 VALUES LESS THAN (to_date('20110605','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110605 VALUES LESS THAN (to_date('20110606','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110606 VALUES LESS THAN (to_date('20110607','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110607 VALUES LESS THAN (to_date('20110608','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110608 VALUES LESS THAN (to_date('20110609','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110609 VALUES LESS THAN (to_date('20110610','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110610 VALUES LESS THAN (to_date('20110611','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110611 VALUES LESS THAN (to_date('20110612','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110612 VALUES LESS THAN (to_date('20110613','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110613 VALUES LESS THAN (to_date('20110614','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110614 VALUES LESS THAN (to_date('20110615','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110615 VALUES LESS THAN (to_date('20110616','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110616 VALUES LESS THAN (to_date('20110617','YYYYMMDD'));

3.执行重定义

SQL> Begin
  2 dbms_redefinition.can_redef_table(uname => 'HXL',
  3 tname =>'TB_HXL_USER',
  4 options_flag => dbms_redefinition.cons_use_rowid);
  5 End;
  6 /

PL/SQL procedure successfully completed.

SQL> Begin
  2 dbms_redefinition.start_redef_table(uname =>'HXL' ,
  3 orig_table => 'TB_HXL_USER',
  4 int_table => 'TB_HXL_USER_MID',
  5 options_flag => dbms_redefinition.cons_use_rowid
  6 );
  7 End;
  8 /

PL/SQL procedure successfully completed.

4.中断重定义后

步骤3执行成功，说明目前该表能执行重定义，现在中断重定义，在中间表新增分区后再重定义。

SQL> Begin
  2 dbms_redefinition.abort_redef_table(uname =>'HXL'
  3 ,orig_table => 'TB_HXL_USER'
  4 ,int_table => 'TB_HXL_USER_MID'
  5 );
  6 End;

PL/SQL procedure successfully completed.

新增14个分区

alter table TB_HXL_USER_MID add partition p_20110617 VALUES LESS THAN (to_date('20110618','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110618 VALUES LESS THAN (to_date('20110619','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110619 VALUES LESS THAN (to_date('20110620','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110620 VALUES LESS THAN (to_date('20110621','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110621 VALUES LESS THAN (to_date('20110622','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110622 VALUES LESS THAN (to_date('20110623','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110623 VALUES LESS THAN (to_date('20110624','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110624 VALUES LESS THAN (to_date('20110625','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110625 VALUES LESS THAN (to_date('20110626','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110626 VALUES LESS THAN (to_date('20110627','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110627 VALUES LESS THAN (to_date('20110628','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110628 VALUES LESS THAN (to_date('20110629','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110629 VALUES LESS THAN (to_date('20110630','YYYYMMDD'));
alter table TB_HXL_USER_MID add partition p_20110630 VALUES LESS THAN (to_date('20110701','YYYYMMDD'));

5.新增分区后再次重定义

这个时候过程抛出了异常，这个是因为创建中间表的DDL的SQL太长了，oracle截掉了一部分后导致的。该问题在11.2.0.2.上已经修复。

SQL> Begin
  2 dbms_redefinition.can_redef_table(uname => 'HXL',
  3 tname =>'TB_HXL_USER',
  4 options_flag => dbms_redefinition.cons_use_rowid);
  5 End;
  6 /

PL/SQL procedure successfully completed.

SQL> Begin
  2 dbms_redefinition.start_redef_table(uname =>'HXL' ,
  3 orig_table => 'TB_HXL_USER',
  4 int_table => 'TB_HXL_USER_MID',
  5 options_flag => dbms_redefinition.cons_use_rowid
  6 );
  7 End;
  8 /
Begin
*
ERROR at line 1:
ORA-00907: missing right parenthesis
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-06512: at line 2