Oracle - Clone table - Structure, data constraints and all

oracle, oracle10g, sql

Solution

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.

Problem

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.

Original source