How to duplicate a table with all its constrains in SQL*Plus?
13,723
I'd start with something like
set long 100000
select dbms_metadata.get_ddl('TABLE', 'TAB1', '<schemaname'>) from dual
This returns a create table
statement for TAB1
(in schema <schemaname>). You can
then copy that statement and change the identfier TAB1 to TAB2. You should make sure that
you also change the names of all constraints since they must be unique in Oracle.
Finally, you'll want to do a insert into TAB2 select * from TAB1
Author by
Moeb
Updated on June 06, 2022Comments
-
Moeb almost 2 years
Using
create table tab2 as select * from tab1;
, I am able to copy the data but not the primary key constraint :SQL> desc tab1; Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER NAME VARCHAR2(20) SQL> select * from tab1; ID NAME ---------- -------------------- 1 A SQL> create table tab2 as select * from tab1; Table created. SQL> desc tab2; Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER NAME VARCHAR2(20) SQL> select * from tab2; ID NAME ---------- -------------------- 1 A SQL>
How can I copy the table with all its constraints as well?