Oracle - Clone table - Structure, data constraints

Question

I know I can copy a tale structure and data by

create table testtable1 as select * from sourcetable

Is there any way to actually clone everything, triggers, constraints, grants etc?

Thanks in advance. We are running 10G.

Answer 1

The following query returns an error:

select dbms_metadata.get_dependent_ddl('TABLE', 'SOURCETABLE') from dual;

Error:

ORA-31608: specified object of type TABLE not found
ORA-06512: at "SYS.DBMS_METADATA", line 6069
ORA-06512: at "SYS.DBMS_METADATA", line 8761
ORA-06512: at line 1
31608. 0000- "specified object of type %s not found"
*Cause: The specified object was not found in the database
*Action: Correct the object specificationand try the call again.

The query below returns a creation code for "SOURCETABLE" as expected.

select dbms_metadata.get_ddl('TABLE', 'SOURCETABLE') from dual;

Answer 2

Take a look into dbms_metadata, especially its procedure dbms_metadata.get_ddl function (see this tahiti link).

So, in your case, you would first do a

select dbms_metadata.get_ddl('TABLE', 'SOURCETABLE') from dual;

As per be here now's comment: dont forget the dbms_metadata.get_dependent_ddl:

select dbms_metadata.get_dependent_ddl('TABLE', 'SOURCETABLE') from dual;

And then work from the given output.