Categories
plsql sql

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

By Laurent Schneider

Oracle Certified Master

5 replies on “better than CTAS”

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

Leave a Reply

Your email address will not be published.