如何在DBMS_REDEFINITION中从(select *)代码创建临时表

Question

我想从DBMS_Redefinition进程的主表创建临时表。

但要求不是编写完整的create语法，因为有n个表，我不能每次都取每个值。

INTERIM TABLE = table_INTER
MAIN_TABLE = table_MAIN

例如..

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

在上述语法中，不能每次都给出列名的值。所以我所做的是

create table table_INTER as (select * from table_MAIN);

但是谁能告诉我如何在这个临时表(table_INTER)中引入分区？

Answer 1

你能试试这个吗？

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