Take the 2-minute tour ×
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. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

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)

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.

share|improve this answer
add comment

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
add comment

Your Answer

 
discard

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.