Sometimes doing a CREATE TABLE AS SELECT is all we need to copy the data from an existing table. But what if we want more than that ? What if we really want to clone that table to match the original as closely as possible. We had a question along these lines on AskTOM today. A standard CTAS copies the NOT NULL attributes and the data types, but not really much else. We know that Data Pump will take care of it, but that is more complex than a simple CTAS.
So here is a simple routine to wrap the Data Pump calls so that the CTAS can be achieved with just as simple a command. A database link pointing back to the same database is all we need.
Note: The true innovation in this blog post came from Laurent’s excellent idea here. I am just adding a small wrapper to make the process a little more palatable. So all credit to Laurent here please.
SQL> create table emp as select * from scott.emp;
Table created.
SQL> create sequence seq start with 8000;
Sequence created.
SQL> alter table emp modify empno default seq.nextval;
Table altered.
SQL> alter table emp add primary key ( empno );
Table altered.
SQL> alter table emp add unique ( ename );
Table altered.
SQL> alter table emp compress;
Table altered.
SQL> alter table emp enable row movement;
Table altered.
And here is the routine to clone it, whilst keeping all of those additional bits of metadata.
SQL> create or replace
2 procedure clone_tab(p_source varchar2,p_target varchar2) is
3 n number;
4 g varchar2(30);
5 j varchar2(30);
6 begin
7 select global_name into g from global_name;
8 begin
9 execute immediate 'alter session close database link tmp$1';
10 exception
11 when others then null;
12 end;
13
14 begin
15 execute immediate 'drop database link tmp$1';
16 exception
17 when others then null;
18 end;
19
20 execute immediate 'create database link tmp$1 using '''||g||'''';
21
22 if p_target like '%.%' or p_source like '%.%' then
23 raise_application_error(-20000,'No schema prefix allowed');
24 end if;
25
26 n := dbms_datapump.open('IMPORT','TABLE','TMP$1');
27 dbms_datapump.metadata_filter(n,'NAME_LIST',''''||upper(p_source)||'''');
28 dbms_datapump.metadata_remap(n,'REMAP_TABLE',upper(p_source),upper(p_target));
29 dbms_datapump.start_job(n);
30 dbms_datapump.wait_for_job(n, j);
31 end;
32 /
Procedure created.
SQL>
SQL> set serverout on
SQL> exec clone_tab('emp','emp2');
PL/SQL procedure successfully completed.
SQL>
SQL> select dbms_metadata.get_ddl('TABLE','EMP2',user) from dual
2
SQL> select dbms_metadata.get_ddl('TABLE','EMP2',user) from dual;
DBMS_METADATA.GET_DDL('TABLE','EMP2',USER)
---------------------------------------------------------------------------------------
CREATE TABLE "MCDONAC"."EMP2"
( "EMPNO" NUMBER(4,0) DEFAULT "MCDONAC"."SEQ"."NEXTVAL" NOT NULL ENABLE,
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
UNIQUE ("ENAME")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE,
PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
COMPRESS BASIC LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE ROW MOVEMENT
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> select count(*) from emp2;
COUNT(*)
----------
14
Recent comments
1 year 45 weeks ago
2 years 5 weeks ago
2 years 9 weeks ago
2 years 10 weeks ago
2 years 14 weeks ago
2 years 36 weeks ago
3 years 4 weeks ago
3 years 33 weeks ago
4 years 18 weeks ago
4 years 18 weeks ago