Copy complete structure of a table in Oracle without using Backup

oracle

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?

Best Answer

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.

Related Question