Re: easiest way to clone a table?

From: Mark D Powell <>
Date: Wed, 24 Sep 2008 12:43:49 -0700 (PDT)
Message-ID: <>

On Sep 23, 9:39 pm, (Malcolm Dew-Jones) wrote:
> 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
> 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.

No need to loop. The get_dependent_ddl procedure will produce DDL for all indexes of a table.


dbms_metadata.get_dependent_ddl('INDEX',upper('&tbl_nm'),upper('&tbl_owner')) from

HTH -- Mark D Powell -- Received on Wed Sep 24 2008 - 14:43:49 CDT

Original text of this message