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

hxl 发表于 2011-12-22 08:54

<DIV>在使用dbms_redefinition将普通表转换为分区表的时候,若转换过程中创建中间表的DDL太长,Oracle会截取掉部分DDL，这个问题在11.2.0.2版本已经修复，以下是在11.2.0.1下测试的。</DIV>
<DIV>测试如下：</DIV>
<DIV>测试Oracle版本:</DIV>
<DIV>
<DIV id=codeText class=codeText>
<OL style="PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 5px" class=dp-css>
<LI>SQL> select * from v$version; </LI>
<LI> </LI>
<LI>BANNER </LI>
<LI>-------------------------------------------------------------------------------- </LI>
<LI> </LI>
<LI>Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production </LI>
<LI>PL/SQL Release 11.2.0.1.0 - Production </LI>
<LI>CORE 11.2.0.1.0 Production </LI>
<LI>TNS for 32-bit Windows: Version 11.2.0.1.0 - Production </LI>
<LI>NLSRTL Version 11.2.0.1.0 - Production</LI></OL></DIV></DIV>
<DIV>1.创建需要重定义的表</DIV>
<DIV>
<DIV id=codeText class=codeText>
<OL style="PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 5px" class=dp-css>
<LI>create table TB_HXL_USER </LI>
<LI>( </LI>
<LI>  STATEDATE DATE not null, </LI>
<LI>  USERNUMBER VARCHAR2(13) not null, </LI>
<LI>  PROVCODE NUMBER not null, </LI>
<LI>  REM1 VARCHAR2(100), </LI>
<LI>  REM2 VARCHAR2(100) default 'A' not null, </LI>
<LI>  REM3 VARCHAR2(100), </LI>
<LI>  REM4 VARCHAR2(100), </LI>
<LI>  REM5 VARCHAR2(100), </LI>
<LI>  REM6 VARCHAR2(100), </LI>
<LI>  REM7 VARCHAR2(100), </LI>
<LI>  REM8 VARCHAR2(100), </LI>
<LI>  REM9 VARCHAR2(100), </LI>
<LI>  REM10 VARCHAR2(100), </LI>
<LI>  CREATE_DATE DATE not null, </LI>
<LI>  CREATE_BY NUMBER not null, </LI>
<LI>  LAST_UPDATE_DATE DATE not null, </LI>
<LI>  LAST_UPDATE_BY NUMBER not null </LI>
<LI>); </LI>
<LI> </LI>
<LI>create unique index IDX_TB_HXL_USER_U1 on TB_HXL_USER (STATEDATE, USERNUMBER, PROVCODE) </LI>
<LI>;</LI></OL></DIV></DIV>
2.创建中间表
中间表目前是33个分区
<DIV id=codeText class=codeText>
<OL style="PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 5px" class=dp-css>
<LI>-- Create table </LI>
<LI> </LI>
<LI>create table tb_hxl_user_mid </LI>
<LI>( </LI>
<LI>  STATEDATE DATE not null, </LI>
<LI>  USERNUMBER VARCHAR2(13) not null, </LI>
<LI>  PROVCODE NUMBER not null, </LI>
<LI>  REM1 VARCHAR2(100), </LI>
<LI>  REM2 VARCHAR2(100) default 'A' not null, </LI>
<LI>  REM3 VARCHAR2(100), </LI>
<LI>  REM4 VARCHAR2(100), </LI>
<LI>  REM5 VARCHAR2(100), </LI>
<LI>  REM6 VARCHAR2(100), </LI>
<LI>  REM7 VARCHAR2(100), </LI>
<LI>  REM8 VARCHAR2(100), </LI>
<LI>  REM9 VARCHAR2(100), </LI>
<LI>  REM10 VARCHAR2(100), </LI>
<LI>  CREATE_DATE DATE not null, </LI>
<LI>  CREATE_BY NUMBER not null, </LI>
<LI>  LAST_UPDATE_DATE DATE not null, </LI>
<LI>  LAST_UPDATE_BY NUMBER not null </LI>
<LI>) </LI>
<LI>PARTITION BY RANGE (STATEDATE) SUBPARTITION BY list (PROVCODE) </LI>
<LI>SUBPARTITION TEMPLATE </LI>
<LI>         (SUBPARTITION p_01 values(01), </LI>
<LI>          SUBPARTITION p_02 values(02), </LI>
<LI>          SUBPARTITION p_03 values(03), </LI>
<LI>          SUBPARTITION p_04 values(04), </LI>
<LI>          SUBPARTITION p_05 values(05), </LI>
<LI>          SUBPARTITION p_06 values(06), </LI>
<LI>          SUBPARTITION p_07 values(07), </LI>
<LI>          SUBPARTITION p_08 values(08), </LI>
<LI>          SUBPARTITION p_09 values(09), </LI>
<LI>          SUBPARTITION p_10 values(10), </LI>
<LI>          SUBPARTITION p_11 values(11), </LI>
<LI>          SUBPARTITION p_12 values(12), </LI>
<LI>          SUBPARTITION p_13 values(13), </LI>
<LI>          SUBPARTITION p_14 values(14), </LI>
<LI>          SUBPARTITION p_15 values(15), </LI>
<LI>          SUBPARTITION p_16 values(16), </LI>
<LI>          SUBPARTITION p_17 values(17), </LI>
<LI>          SUBPARTITION p_18 values(18), </LI>
<LI>          SUBPARTITION p_19 values(19), </LI>
<LI>          SUBPARTITION p_20 values(20), </LI>
<LI>          SUBPARTITION p_21 values(21), </LI>
<LI>          SUBPARTITION p_22 values(22), </LI>
<LI>          SUBPARTITION p_23 values(23), </LI>
<LI>          SUBPARTITION p_24 values(24), </LI>
<LI>          SUBPARTITION p_25 values(25), </LI>
<LI>          SUBPARTITION p_26 values(26), </LI>
<LI>          SUBPARTITION p_27 values(27), </LI>
<LI>          SUBPARTITION p_28 values(28), </LI>
<LI>          SUBPARTITION p_29 values(29), </LI>
<LI>          SUBPARTITION p_30 values(30) </LI>
<LI>         ) </LI>
<LI>   (PARTITION p_20110515 VALUES LESS THAN (to_date('20110516','YYYYMMDD')) </LI>
<LI>    ); </LI>
<LI> </LI>
<LI>-- Create/Recreate indexes </LI>
<LI> </LI>
<LI>create unique index IDX_TB_HXL_USER_MID_U1 </LI>
<LI>on TB_HXL_USER_MID (STATEDATE, USERNUMBER, PROVCODE) local; </LI>
<LI> </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110516 VALUES LESS THAN (to_date('20110517','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110517 VALUES LESS THAN (to_date('20110518','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110518 VALUES LESS THAN (to_date('20110519','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110519 VALUES LESS THAN (to_date('20110520','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110520 VALUES LESS THAN (to_date('20110521','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110521 VALUES LESS THAN (to_date('20110522','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110522 VALUES LESS THAN (to_date('20110523','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110523 VALUES LESS THAN (to_date('20110524','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110524 VALUES LESS THAN (to_date('20110525','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110525 VALUES LESS THAN (to_date('20110526','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110526 VALUES LESS THAN (to_date('20110527','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110527 VALUES LESS THAN (to_date('20110528','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110528 VALUES LESS THAN (to_date('20110529','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110529 VALUES LESS THAN (to_date('20110530','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110530 VALUES LESS THAN (to_date('20110531','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110531 VALUES LESS THAN (to_date('20110601','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110601 VALUES LESS THAN (to_date('20110602','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110602 VALUES LESS THAN (to_date('20110603','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110603 VALUES LESS THAN (to_date('20110604','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110604 VALUES LESS THAN (to_date('20110605','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110605 VALUES LESS THAN (to_date('20110606','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110606 VALUES LESS THAN (to_date('20110607','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110607 VALUES LESS THAN (to_date('20110608','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110608 VALUES LESS THAN (to_date('20110609','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110609 VALUES LESS THAN (to_date('20110610','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110610 VALUES LESS THAN (to_date('20110611','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110611 VALUES LESS THAN (to_date('20110612','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110612 VALUES LESS THAN (to_date('20110613','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110613 VALUES LESS THAN (to_date('20110614','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110614 VALUES LESS THAN (to_date('20110615','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110615 VALUES LESS THAN (to_date('20110616','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110616 VALUES LESS THAN (to_date('20110617','YYYYMMDD'));</LI></OL></DIV>
3.执行重定义
<DIV id=codeText class=codeText>
<OL style="PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 5px" class=dp-css>
<LI>SQL> Begin </LI>
<LI>  2 dbms_redefinition.can_redef_table(uname => 'HXL', </LI>
<LI>  3 tname =>'TB_HXL_USER', </LI>
<LI>  4 options_flag => dbms_redefinition.cons_use_rowid); </LI>
<LI>  5 End; </LI>
<LI>  6 / </LI>
<LI> </LI>
<LI>PL/SQL procedure successfully completed. </LI>
<LI> </LI>
<LI>SQL> Begin </LI>
<LI>  2 dbms_redefinition.start_redef_table(uname =>'HXL' , </LI>
<LI>  3 orig_table => 'TB_HXL_USER', </LI>
<LI>  4 int_table => 'TB_HXL_USER_MID', </LI>
<LI>  5 options_flag => dbms_redefinition.cons_use_rowid </LI>
<LI>  6 ); </LI>
<LI>  7 End; </LI>
<LI>  8 / </LI>
<LI> </LI>
<LI>PL/SQL procedure successfully completed.</LI></OL></DIV>
4.中断重定义后
步骤3执行成功，说明目前该表能执行重定义，现在中断重定义，在中间表新增分区后再重定义。
<DIV id=codeText class=codeText>
<OL style="PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 5px" class=dp-css>
<LI>SQL> Begin </LI>
<LI>  2 dbms_redefinition.abort_redef_table(uname =>'HXL' </LI>
<LI>  3 ,orig_table => 'TB_HXL_USER' </LI>
<LI>  4 ,int_table => 'TB_HXL_USER_MID' </LI>
<LI>  5 ); </LI>
<LI>  6 End; </LI>
<LI>   </LI>
<LI>PL/SQL procedure successfully completed.</LI></OL></DIV>
新增14个分区
<DIV id=codeText class=codeText>
<OL style="PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 5px" class=dp-css>
<LI>alter table TB_HXL_USER_MID add partition p_20110617 VALUES LESS THAN (to_date('20110618','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110618 VALUES LESS THAN (to_date('20110619','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110619 VALUES LESS THAN (to_date('20110620','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110620 VALUES LESS THAN (to_date('20110621','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110621 VALUES LESS THAN (to_date('20110622','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110622 VALUES LESS THAN (to_date('20110623','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110623 VALUES LESS THAN (to_date('20110624','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110624 VALUES LESS THAN (to_date('20110625','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110625 VALUES LESS THAN (to_date('20110626','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110626 VALUES LESS THAN (to_date('20110627','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110627 VALUES LESS THAN (to_date('20110628','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110628 VALUES LESS THAN (to_date('20110629','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110629 VALUES LESS THAN (to_date('20110630','YYYYMMDD')); </LI>
<LI>alter table TB_HXL_USER_MID add partition p_20110630 VALUES LESS THAN (to_date('20110701','YYYYMMDD'));</LI></OL></DIV>
5.新增分区后再次重定义
这个时候过程抛出了异常，这个是因为创建中间表的DDL的SQL太长了，oracle截掉了一部分后导致的。该问题在11.2.0.2.上已经修复。
<DIV id=codeText class=codeText>
<OL style="PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 5px" class=dp-css>
<LI>SQL> Begin </LI>
<LI>  2 dbms_redefinition.can_redef_table(uname => 'HXL', </LI>
<LI>  3 tname =>'TB_HXL_USER', </LI>
<LI>  4 options_flag => dbms_redefinition.cons_use_rowid); </LI>
<LI>  5 End; </LI>
<LI>  6 / </LI>
<LI> </LI>
<LI>PL/SQL procedure successfully completed. </LI>
<LI> </LI>
<LI>SQL> Begin </LI>
<LI>  2 dbms_redefinition.start_redef_table(uname =>'HXL' , </LI>
<LI>  3 orig_table => 'TB_HXL_USER', </LI>
<LI>  4 int_table => 'TB_HXL_USER_MID', </LI>
<LI>  5 options_flag => dbms_redefinition.cons_use_rowid </LI>
<LI>  6 ); </LI>
<LI>  7 End; </LI>
<LI>  8 / </LI>
<LI>Begin </LI>
<LI>* </LI>
<LI>ERROR at line 1: </LI>
<LI>ORA-00907: missing right parenthesis </LI>
<LI>ORA-06512: at "SYS.DBMS_REDEFINITION", line 52 </LI>
<LI>ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646 </LI>
<LI>ORA-06512: at line 2</LI></OL></DIV>

页: [1]

Chinaunix's Archiver

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