Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.


create table t1 select * from table2;

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

share|improve this question
up vote 2 down vote accepted
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)


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.

share|improve this answer
I think you accidentally swapped the two last arguments. The correct statement seems to be select dbms_metadata.get_ddl('TABLE','TABLENAME','SCHEMANAME') DDL from dual; – blubb May 3 at 11:00

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...

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.