Re: easiest way to clone a table?

On Sep 23, 9:39 pm, yf..._at_vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote:
> m..._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.

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

select  

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

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