How to duplicate a table with all its constrains in SQL*Plus?

Question

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?

Answer 1

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