better than CTAS


SQL> create table t1(x number primary key);
Table created.
SQL> desc t1
Name Null? Type
----------------------- -------- ----------------
X NOT NULL NUMBER


SQL> create table t2 as select * from t1;
Table created.
SQL> desc t2
Name Null? Type
----------------------- -------- ----------------
X NUMBER

The table T2 has the column X, but not the constraint (primary key / not null).

If you want to do a create table as select but want to keep index / constraints etc, then you rather use datapump

SQL> set autop on
SQL> var job_state varchar2(30)
SQL> declare
n number;
begin
n := DBMS_DATAPUMP.open('IMPORT','TABLE','DB01');
DBMS_DATAPUMP.metadata_filter(n,'NAME_LIST','''T1''');
DBMS_DATAPUMP.metadata_remap(n,'REMAP_TABLE','T1','T3');
DBMS_DATAPUMP.start_job(n);
DBMS_DATAPUMP.WAIT_FOR_JOB(n, :job_state);
end;
/
PL/SQL procedure successfully completed.
JOB_STATE
--------------------------------------------------
COMPLETED
SQL> desc t3
Name Null? Type
----------------------- -------- ----------------
X NOT NULL NUMBER

The Table T3 is a copy of T1. DB01 is my implicit loopback database link (database name).

5 thoughts on “better than CTAS”

  2. Laurent Schneider Post author

    thanks for the hint. indeed, you need to rename those too


    SQL> drop table t1
    Table dropped.
    SQL> drop table t2
    Table dropped.
    SQL> create table t1(x number constraint p1 primary key
    using index (create unique index i1 on t1(x)) )
    Table created.
    SQL> set autop on
    SQL> var job_state varchar2(30)
    SQL> declare
    n number;
    begin
    n := DBMS_DATAPUMP.open ('IMPORT', 'TABLE', 'DB01');
    DBMS_DATAPUMP.metadata_filter(n, 'NAME_LIST','''T1''');
    DBMS_DATAPUMP.metadata_remap(n, 'REMAP_TABLE','T1','T2');
    DBMS_DATAPUMP.metadata_remap(n, 'REMAP_TABLE','P1','P2');
    DBMS_DATAPUMP.metadata_remap(n, 'REMAP_TABLE','I1','I2');
    DBMS_DATAPUMP.start_job (n);
    DBMS_DATAPUMP.WAIT_FOR_JOB(n, :job_state);
    end;
    PL/SQL procedure successfully completed.

    job_state
    ------------------------------
    COMPLETED
    SQL> select dbms_metadata.get_ddl('TABLE','T2') from dual
    DBMS_METADATA.GET_DDL('TABLE','T2')
    ---------------------------------------
    CREATE TABLE "SCOTT"."T2"
    ( "X" NUMBER,
    CONSTRAINT "P2" PRIMARY KEY ("X")
    USING INDEX (
    CREATE UNIQUE INDEX "SCOTT"."I2" ON "SCOTT"."T2" ("X")
    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
    TABLESPACE "ADMIN" ) ENABLE
    ) SEGMENT CREATION DEFERRED
    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
    NOCOMPRESS LOGGING
    TABLESPACE "ADMIN"

  4. quanwenzhao

    Hello,Laurent Schneider

    About your implicit loopback dblink ‘DB01’,how to create it?
    and my own is as follows,

    SQL> set linesize 200
    SQL> col global_name for a30
    SQL> select * from global_name;

    GLOBAL_NAME
    ——————————
    ORA11GR2

    SQL> create database link db01 using ‘ora11gr2’;

    Database link created.

    SQL> select * from dba_db_links;

    OWNER DB_LINK USERNAME HOST
    ———- ———– ————— ———–
    SYS DB01 ora11gr2

    SQL> set autop on
    SQL> var job_state varchar2(30)
    SQL> declare
    2 n number;
    3 begin
    4 n := DBMS_DATAPUMP.open(‘IMPORT’,’TABLE’,’DB01′);
    5 DBMS_DATAPUMP.metadata_filter(n,’NAME_LIST’,”’T1”’);
    6 DBMS_DATAPUMP.metadata_remap(n,’REMAP_TABLE’,’T1′,’T3′);
    7 DBMS_DATAPUMP.start_job(n);
    8 DBMS_DATAPUMP.WAIT_FOR_JOB(n,:job_state);
    9 end;
    10 /
    declare
    *
    ERROR at line 1:
    ORA-39001: Invalid parameter value
    ORA-06512: In “SYS.DBMS_SYS_ERROR”, line 79
    ORA-06512: In “SYS.DBMS_DATAPUMP”, line 3507
    ORA-06512: In “SYS.DBMS_DATAPUMP”, line 5296
    ORA-06512: In line 4

  5. Laurent Schneider Post author

    You shouldn’t need to create it, simply use ora11gr2

    I think you got something wrong with quotes, it may be a copy paste issue

    There are three single quotes before T1

Comments are closed.