Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle - Clone table - Structure, data constraints and all

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.

like image 731
Dr.Avalanche Avatar asked Nov 14 '12 10:11

Dr.Avalanche


1 Answers

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.

like image 157
René Nyffenegger Avatar answered Oct 24 '22 04:10

René Nyffenegger