Re: easiest way to clone a table?

mh_at_pixar.com wrote:
: Given a table FOO, what is the easiest way to create a
: table COPY_OF_FOO that has identical schema, indexes, etc.?

: Preferrably something that can be scripted or performed
: programatically, and not via Toad, etc.
: etc.

: Many TIA!
: Mark.

I have used code like the following to generate table create statements. If I want to create the tables using new names I usually pass the result through a perl script, but you can also manipulate the result directly with SQL functions such as REPLACE as part of the select.

col ddl format a400

execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM ,'SEGMENT_ATTRIBUTES',false);

execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM ,'SQLTERMINATOR',true);

select dbms_metadata.get_ddl('TABLE',table_name) DDL from user_tables where table_name like 'whatever%' order by table_name /

The resulting scripts would build indexes for PK,UK, and FK constraints, but not other indexes.

You can use dbms_metadata to get code for many objects besides tables, so I would look at the parameters (assuming they exist) to use when looping over user_indexes.

When you create the tables then some won't create due to FK dependencies. If you looked at user_dependencies you could order the result so that you don't get those errors. Otherwise just run the result multiple times until you don't get any more errors. Received on Tue Sep 23 2008 - 20:39:00 CDT