Copy complete structure of a table in Oracle without using Backup

Question

When you create do a CTAS (create table as select) of a table you only get the structure, but lose the index, PK, FK, etc.

Example:

create table t1 select * from table2;

How can a copy of the table structure be made that includes these things without doing a backup?

Answer 1

select dbms_metadata.get_ddl('TABLE','SCHEMANAME','TABLENAME') DDL from dual;

TABLE is the type of object (you can also extract INDEX, VIEW, FUNCTION, PROCEDURE etc)

or:

create table t1 as
( select * from t2 where 3=4 );

dbms_metadata.get_ddl is the way to go, as it preserves various aspects that a CTAS query doesn't.

Answer 2

Phil's answer is good and probably what you want. SQL Developer provides a wizard for this functionality from the Tools menu.

Depending on your requirements you may want to do an export (which uses the dbms_metadata package). The Oracle Concepts Guide (required reading for anyone using Oracle) has a section on Oracle Data Pump Export and Import (emphasis mine).

Oracle Data Pump enables high-speed movement of data and metadata...

Answer 3

There are basically 3 ways to copy the structure and data to another tables

1. Create table myschema.newtable as select * from anotherschema.oldtable where 1 = 1; /* this copies all the data or 1 = 2 copies no data but creates the table */ this will not work for Oracle 12 and above if you have NVARCHAR (2000) and above, CLOBS or BLOBS or extended varchar2 enabled over 32,767 as an invisible constraint index is created.
2. use the Oracle metatable (select dbms_metadata.get_ddl('TABLE','SCHEMANAME','TABLENAME') DDL from dual; This works will except, if you have primary keys, contraints, etc. With Oracle 12.2 or your mirroring a table on a remote database using a database link. What happens is the table definition is spread out over multiple lines and you only retrieve the latest. So, you have to write complex code to loop thru the definition if there are mulitple lines. It's a real headache. Not worth the trouble if you are working with Oracle 12.2 or above.
3. If you are working with Oracle 12.2 or higher, for example 19c, This will work whether the source table is partitioned or not. But, the source table must be local an not remote using a DB link. I have not seen this technique documented for non-partitioned source tables anywhere. But, it works for partitioned and non-partitioned source tables! CREATE TABLE MYSCHEMA.NEW_TMP FOR EXCHANGE WITH TABLE MYSCHEMA.SOURCE_TABLE; This will mirror the columns exactly so that you can copy the source's data into the new table based on a filter. It will not copy over the PK's or indexes. As a note, partition exchange is one of the fastest ways to load data. It's virtually instantaneous. It's worth researching.