OraFAQ Forum: Server Administration » Dbms

Home » RDBMS Server » Server Administration » Dbms_Redefinition issue (11g windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Dbms_Redefinition issue [message #520317]

Sun, 21 August 2011 05:10

andy huang
Messages: 498
Registered: July 2011

Senior Member

Dear all,
The table TB_HXL_USER is a no-partition table and TB_HXL_USER_MID is rang+list partition table,and TB_HXL_USER_MID have 32 partitions and 960(32*30) subpartitions. and TB_HXL_USER_MID have a unqiue index(local index),when i redefine TB_HXL_USER to TB_HXL_USER_MID,it raise error? why?

SQL> Begin
  2
  3    Dbms_Redefinition.Start_Redef_Table(Uname        => User,
  4                                        Orig_Table => 'TB_HXL_USER',
  5                                        Int_Table    => 'TB_HXL_USER_MID',
  6                                        Col_Mapping  =>Null,
  7    Options_Flag => Dbms_Redefinition.Cons_Use_Pk);
  8  End;
  9  /
Begin
*
ERROR at line 1:
ORA-14150: missing SUBPARTITION keyword
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1634
ORA-06512: at line 3

Report message to a moderator

Re: Dbms_Redefinition issue [message #520318 is a reply to message #520317]

Sun, 21 August 2011 05:15

andy huang
Messages: 498
Registered: July 2011

Senior Member

Hi,
The create-sql of mid-table tb_hxl_user_mid is as flowing:

-- 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'));

Report message to a moderator

Re: Dbms_Redefinition issue [message #520319 is a reply to message #520318]

Sun, 21 August 2011 05:36

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

It works for me:

orcl> exec dbms_redefinition.can_redef_table('jon','tb_hxl_user')

PL/SQL procedure successfully completed.

orcl> exec Dbms_Redefinition.Start_Redef_Table('jon','tb_hxl_user','tb_hxl_user_mid')

PL/SQL procedure successfully completed.

orcl>

I had to guess at the DDL for your originial table and constraint:

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
;

alter table tb_hxl_user add constraint pk primary key (STATEDATE, USERNUMBER, PROVCODE);

Release:

orcl> select * from v$version;

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

orcl>

Report message to a moderator

Re: Dbms_Redefinition issue [message #520333 is a reply to message #520319]

Sun, 21 August 2011 20:02

andy huang
Messages: 498
Registered: July 2011

Senior Member

hi,
Can you create a local index and try it?

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

Report message to a moderator

Re: Dbms_Redefinition issue [message #520357 is a reply to message #520333]

Mon, 22 August 2011 02:14

andy huang
Messages: 498
Registered: July 2011

Senior Member

Dear all,
My full test is as flowing,when i add more partition in mid-table,it raise error.

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)
;

-- 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'));


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.

-- there is ok, when the total count of partition is 33

-- I abort the procedure and try to next test when add some partitions

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.

now i add some partitions in mid-table and try to redefine table tb_hxl_user

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'));


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

Report message to a moderator

Re: Dbms_Redefinition issue [message #520358 is a reply to message #520333]

Mon, 22 August 2011 02:14

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Well, no way is it going to work if you abort the procedure in the middle, and then try to continue. And I am rather irritated that you concealed that information in your original question.

[Updated on: Mon, 22 August 2011 02:18]

Report message to a moderator

Re: Dbms_Redefinition issue [message #520359 is a reply to message #520357]

Mon, 22 August 2011 02:17

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Maybe we can help more if you post your Oracle version as John did it.

Regards
Michel

Report message to a moderator

Re: Dbms_Redefinition issue [message #520360 is a reply to message #520358]

Mon, 22 August 2011 02:24

andy huang
Messages: 498
Registered: July 2011

Senior Member

Hi,
Can you try to test my full test,when add more partitions in mid-table,it raise error!

Report message to a moderator

Re: Dbms_Redefinition issue [message #520361 is a reply to message #520360]

Mon, 22 August 2011 02:26

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Can you post your Oracle version?
It is useless John (or anyone) wastes his time if he does not run the same version than you.

Regards
Michel

Report message to a moderator

Re: Dbms_Redefinition issue [message #520363 is a reply to message #520361]

Mon, 22 August 2011 02:37

andy huang
Messages: 498
Registered: July 2011

Senior Member

hi,

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

Report message to a moderator

Re: Dbms_Redefinition issue [message #520364 is a reply to message #520363]

Mon, 22 August 2011 02:54

Barbara Boehmer
Messages: 9103
Registered: November 2002
Location: California, USA

Senior Member

I can at least confirm the same error in the same version. I tested in the scott schema, so I modified the uname parameters. Below is the complete test script, followed by the run.

select * from v$version
/
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)
;

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 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'));


Begin
    dbms_redefinition.can_redef_table(uname => 'SCOTT',
                                      tname =>'TB_HXL_USER',
                                      options_flag => dbms_redefinition.cons_use_rowid);
End;
/
Begin
   dbms_redefinition.start_redef_table(uname =>'SCOTT' ,
                                       orig_table => 'TB_HXL_USER',
                                       int_table => 'TB_HXL_USER_MID',
                                       options_flag => dbms_redefinition.cons_use_rowid
                                       );
End;
/
Begin
    dbms_redefinition.abort_redef_table(uname =>'SCOTT'
                                        ,orig_table => 'TB_HXL_USER'
                                        ,int_table => 'TB_HXL_USER_MID'
                                        );
End;
/  
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'));


Begin
    dbms_redefinition.can_redef_table(uname => 'SCOTT',
                                      tname =>'TB_HXL_USER',
                                      options_flag => dbms_redefinition.cons_use_rowid);
End;
/
Begin
   dbms_redefinition.start_redef_table(uname =>'SCOTT' ,
                                       orig_table => 'TB_HXL_USER',
                                       int_table => 'TB_HXL_USER_MID',
                                       options_flag => dbms_redefinition.cons_use_rowid
                                       );
End;
/

SCOTT@orcl_11gR2> select * from v$version
  2  /

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

5 rows selected.

SCOTT@orcl_11gR2> create table TB_HXL_USER
  2  (
  3    STATEDATE	DATE not null,
  4    USERNUMBER	VARCHAR2(13) not null,
  5    PROVCODE 	NUMBER not null,
  6    REM1		VARCHAR2(100),
  7    REM2		VARCHAR2(100) default 'A' not null,
  8    REM3		VARCHAR2(100),
  9    REM4		VARCHAR2(100),
 10    REM5		VARCHAR2(100),
 11    REM6		VARCHAR2(100),
 12    REM7		VARCHAR2(100),
 13    REM8		VARCHAR2(100),
 14    REM9		VARCHAR2(100),
 15    REM10		VARCHAR2(100),
 16    CREATE_DATE	DATE not null,
 17    CREATE_BY	NUMBER not null,
 18    LAST_UPDATE_DATE DATE not null,
 19    LAST_UPDATE_BY	NUMBER not null
 20  );

Table created.

SCOTT@orcl_11gR2> 
SCOTT@orcl_11gR2> create unique index IDX_TB_HXL_USER_U1 on TB_HXL_USER (STATEDATE, USERNUMBER, PROVCODE)
  2  ;

Index created.

SCOTT@orcl_11gR2> 
SCOTT@orcl_11gR2> create table tb_hxl_user_mid
  2  (
  3    STATEDATE	DATE not null,
  4    USERNUMBER	VARCHAR2(13) not null,
  5    PROVCODE 	NUMBER not null,
  6    REM1		VARCHAR2(100),
  7    REM2		VARCHAR2(100) default 'A' not null,
  8    REM3		VARCHAR2(100),
  9    REM4		VARCHAR2(100),
 10    REM5		VARCHAR2(100),
 11    REM6		VARCHAR2(100),
 12    REM7		VARCHAR2(100),
 13    REM8		VARCHAR2(100),
 14    REM9		VARCHAR2(100),
 15    REM10		VARCHAR2(100),
 16    CREATE_DATE	DATE not null,
 17    CREATE_BY	NUMBER not null,
 18    LAST_UPDATE_DATE DATE not null,
 19    LAST_UPDATE_BY	NUMBER not null
 20  )
 21  PARTITION BY RANGE (STATEDATE) SUBPARTITION BY list (PROVCODE)
 22  SUBPARTITION TEMPLATE
 23  	      (SUBPARTITION p_01 values(01),
 24  	       SUBPARTITION p_02 values(02),
 25  	       SUBPARTITION p_03 values(03),
 26  	       SUBPARTITION p_04 values(04),
 27  	       SUBPARTITION p_05 values(05),
 28  	       SUBPARTITION p_06 values(06),
 29  	       SUBPARTITION p_07 values(07),
 30  	       SUBPARTITION p_08 values(08),
 31  	       SUBPARTITION p_09 values(09),
 32  	       SUBPARTITION p_10 values(10),
 33  	       SUBPARTITION p_11 values(11),
 34  	       SUBPARTITION p_12 values(12),
 35  	       SUBPARTITION p_13 values(13),
 36  	       SUBPARTITION p_14 values(14),
 37  	       SUBPARTITION p_15 values(15),
 38  	       SUBPARTITION p_16 values(16),
 39  	       SUBPARTITION p_17 values(17),
 40  	       SUBPARTITION p_18 values(18),
 41  	       SUBPARTITION p_19 values(19),
 42  	       SUBPARTITION p_20 values(20),
 43  	       SUBPARTITION p_21 values(21),
 44  	       SUBPARTITION p_22 values(22),
 45  	       SUBPARTITION p_23 values(23),
 46  	       SUBPARTITION p_24 values(24),
 47  	       SUBPARTITION p_25 values(25),
 48  	       SUBPARTITION p_26 values(26),
 49  	       SUBPARTITION p_27 values(27),
 50  	       SUBPARTITION p_28 values(28),
 51  	       SUBPARTITION p_29 values(29),
 52  	       SUBPARTITION p_30 values(30)
 53  	      )
 54  	(PARTITION p_20110515 VALUES LESS THAN (to_date('20110516','YYYYMMDD'))
 55  	 );

Table created.

SCOTT@orcl_11gR2> 
SCOTT@orcl_11gR2> create unique index IDX_TB_HXL_USER_MID_U1
  2  on TB_HXL_USER_MID (STATEDATE, USERNUMBER, PROVCODE) local;

Index created.

SCOTT@orcl_11gR2> 
SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110516 VALUES LESS THAN (to_date('20110517','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110517 VALUES LESS THAN (to_date('20110518','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110518 VALUES LESS THAN (to_date('20110519','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110519 VALUES LESS THAN (to_date('20110520','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110520 VALUES LESS THAN (to_date('20110521','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110521 VALUES LESS THAN (to_date('20110522','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110522 VALUES LESS THAN (to_date('20110523','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110523 VALUES LESS THAN (to_date('20110524','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110524 VALUES LESS THAN (to_date('20110525','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110525 VALUES LESS THAN (to_date('20110526','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110526 VALUES LESS THAN (to_date('20110527','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110527 VALUES LESS THAN (to_date('20110528','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110528 VALUES LESS THAN (to_date('20110529','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110529 VALUES LESS THAN (to_date('20110530','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110530 VALUES LESS THAN (to_date('20110531','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110531 VALUES LESS THAN (to_date('20110601','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110601 VALUES LESS THAN (to_date('20110602','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110602 VALUES LESS THAN (to_date('20110603','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110603 VALUES LESS THAN (to_date('20110604','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110604 VALUES LESS THAN (to_date('20110605','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110605 VALUES LESS THAN (to_date('20110606','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110606 VALUES LESS THAN (to_date('20110607','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110607 VALUES LESS THAN (to_date('20110608','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110608 VALUES LESS THAN (to_date('20110609','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110609 VALUES LESS THAN (to_date('20110610','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110610 VALUES LESS THAN (to_date('20110611','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110611 VALUES LESS THAN (to_date('20110612','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110612 VALUES LESS THAN (to_date('20110613','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110613 VALUES LESS THAN (to_date('20110614','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110614 VALUES LESS THAN (to_date('20110615','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110615 VALUES LESS THAN (to_date('20110616','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110616 VALUES LESS THAN (to_date('20110617','YYYYMMDD'));

Table altered.

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

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> Begin
  2  	dbms_redefinition.start_redef_table(uname =>'SCOTT' ,
  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.

SCOTT@orcl_11gR2> Begin
  2  	 dbms_redefinition.abort_redef_table(uname =>'SCOTT'
  3  					     ,orig_table => 'TB_HXL_USER'
  4  					     ,int_table => 'TB_HXL_USER_MID'
  5  					     );
  6  End;
  7  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110617 VALUES LESS THAN (to_date('20110618','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110618 VALUES LESS THAN (to_date('20110619','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110619 VALUES LESS THAN (to_date('20110620','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110620 VALUES LESS THAN (to_date('20110621','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110621 VALUES LESS THAN (to_date('20110622','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110622 VALUES LESS THAN (to_date('20110623','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110623 VALUES LESS THAN (to_date('20110624','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110624 VALUES LESS THAN (to_date('20110625','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110625 VALUES LESS THAN (to_date('20110626','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110626 VALUES LESS THAN (to_date('20110627','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110627 VALUES LESS THAN (to_date('20110628','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110628 VALUES LESS THAN (to_date('20110629','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110629 VALUES LESS THAN (to_date('20110630','YYYYMMDD'));

Table altered.

SCOTT@orcl_11gR2> alter table TB_HXL_USER_MID add partition p_20110630 VALUES LESS THAN (to_date('20110701','YYYYMMDD'));

Table altered.

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

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> Begin
  2  	dbms_redefinition.start_redef_table(uname =>'SCOTT' ,
  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


SCOTT@orcl_11gR2>

Report message to a moderator

Re: Dbms_Redefinition issue [message #520365 is a reply to message #520363]

Mon, 22 August 2011 02:56

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I executed it in 10.2.0.1, Windows XP SP3 and get the error:

Begin
*
ERROR at line 1:
ORA-14150: missing SUBPARTITION keyword
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-06512: at line 2

Not the same error but it seems an invalid syntax of a generated statement.
As I used MICHEL instead of HXL it is likely the difference comes from this.
I suspect a buffer overflow (statement too long for the buffer).
And it seems to be fixed in 11.2.0.2, so upgrade to this version.

Regards
Michel

Report message to a moderator

Re: Dbms_Redefinition issue [message #520367 is a reply to message #520365]

Mon, 22 August 2011 03:52

andy huang
Messages: 498
Registered: July 2011

Senior Member

Hi,
if the index named IDX_TB_HXL_USER_MID_U1 of mid-table is GLOBAL index,where is no any problem,why?

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)
;

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 unique index IDX_TB_HXL_USER_MID_U1 
on TB_HXL_USER_MID (STATEDATE, USERNUMBER, PROVCODE) global;

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'));

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'));


Begin
    dbms_redefinition.can_redef_table(uname => 'HXL',
                                      tname =>'TB_HXL_USER',
                                      options_flag => dbms_redefinition.cons_use_rowid);
End;
/
PL/SQL procedure successfully completed.

Begin
   dbms_redefinition.start_redef_table(uname =>'HXL' ,
                                       orig_table => 'TB_HXL_USER',
                                       int_table => 'TB_HXL_USER_MID',
                                       options_flag => dbms_redefinition.cons_use_rowid
                                       );
End;
/
PL/SQL procedure successfully completed.

Report message to a moderator

Re: Dbms_Redefinition issue [message #520370 is a reply to message #520367]

Mon, 22 August 2011 04:10

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

A bug is a bug it happens some times and not other times because different ways are taken.
There is nothing to try to understand, just admit it and upgrade or do not use the feature.

Regards
Michel

[Updated on: Mon, 22 August 2011 04:11]

Report message to a moderator

Previous Topic:	Oracle Tablespace
Next Topic:	Flashback Data Archive - Problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jan 27 02:51:49 CST 2025