how to create interim table from (select * ) code in DBMS_REDEFINITION

Question

I want to create Interim table from main table for DBMS_Redefinition Process.

but the requirement is not to write entire create syntax because there are n number of tables and i cant take each value every time.

INTERIM TABLE = table_INTER
MAIN_TABLE = table_MAIN

for eg..

CREATE TABLE table_INTER (
ContactPartKey            NUMBER(20) NOT NULL,
PartyKey                  NUMBER(10) NOT NULL,
ConParticipationRoleKey   NUMBER(10) NOT NULL,
ChannelKey                NUMBER(10) NOT NULL,
StateKey                  NUMBER(10) NOT NULL,
StateReasonKey            NUMBER(10) NOT NULL,
NextStateKey              NUMBER(10) NOT NULL,
PreviousStateKey          NUMBER(10) NOT NULL,
QueueKey                  NUMBER(10) NOT NULL,
RtgPointKey               NUMBER(10) NOT NULL,
ContactPartIndKey         NUMBER(10) NOT NULL,
DeviceKey                 NUMBER(10) NOT NULL,
ContactID                 NUMBER(20) NOT NULL,
Parent1ContactID          NUMBER(20) ,
StartDateKey              NUMBER(10) NOT NULL,
EndDateKey                NUMBER(10) NOT NULL,
StartTimeKey              NUMBER(3)  NOT NULL,
EndTimeKey                NUMBER(3)  NOT NULL,
StartDateTime             DATE       NOT NULL,
EndDateTime               DATE       NOT NULL,
StateDur                  NUMBER(10) NOT NULL,
ContactAllocatedCost      decimal(14,4) ,
ContactPartSeqNum         NUMBER(10) NOT NULL,
ContactInProcessInd       NUMBER(3)  NOT NULL,
FinalPartInd              NUMBER(3)  NOT NULL,
Counter                   NUMBER(10) NOT NULL,
SourceKey                 NUMBER(10) NOT NULL,
StreamKey                 NUMBER(10) NOT NULL,
ProcessKey                NUMBER(10) ,
SelfServiceInd            NUMBER(3)  )
--PRIMARY KEY(ContactPartKey)
TABLESPACE AVAYAIQFACT1
STORAGE (INITIAL 2097152 NEXT 2097152)
PCTFREE 0
PARTITION BY RANGE (STARTDATETIME)
 INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(PARTITION P1 VALUES LESS THAN (TO_DATE('01/09/2013', 'DD/MM/YYYY')) TABLESPACE T1,
 PARTITION P2 VALUES LESS THAN (TO_DATE('01/10/2013', 'DD/MM/YYYY')) TABLESPACE T1);

In above syntax, column name value cant be given every time. So what i did was

create table table_INTER as (select * from table_MAIN);

But can anyone tell me how to introduce partition in this interim table (table_INTER).

score 0 · Answer 1 · answered Oct 03 '13 at 10:40

Can you try this?

CREATE TABLE TABLE_INTER
PARTITION BY RANGE (STARTDATETIME)
    INTERVAL ( NUMTOYMINTERVAL ( 1,
                            'MONTH' ) )
    ( PARTITION P1
          VALUES LESS THAN
              (TO_DATE ( '01/09/2013',
                       'DD/MM/YYYY' ))
          TABLESPACE T1,
      PARTITION P2
          VALUES LESS THAN
              (TO_DATE ( '01/10/2013',
                       'DD/MM/YYYY' ))
          TABLESPACE T1 )
AS
    ( SELECT * FROM TABLE_MAIN );

how to create interim table from (select * ) code in DBMS_REDEFINITION

1 Answers1