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 Comments

  • 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"    
    

  • 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

  • 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

Leave a Reply

Your email address will not be published.

*