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.


--
Mark Harrison
Pixar Animation Studios

mh (AT) pixar (DOT) 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.SE SSION_TRANSFORM
,'SEGMENT_ATTRIBUTES',false);

execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SE SSION_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.

mh (AT) pixar (DOT) 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.SE SSION_TRANSFORM
,'SEGMENT_ATTRIBUTES',false);

execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SE SSION_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.

mh (AT) pixar (DOT) 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.SE SSION_TRANSFORM
,'SEGMENT_ATTRIBUTES',false);

execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SE SSION_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.

mh (AT) pixar (DOT) 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.SE SSION_TRANSFORM
,'SEGMENT_ATTRIBUTES',false);

execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SE SSION_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.

On Sep 23, 9:39*pm, yf... (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones)
wrote:
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('&tb l_nm'),upper('&tbl_owner'))
from
sys.dual;

HTH -- Mark D Powell --

On Sep 23, 9:39*pm, yf... (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones)
wrote:
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('&tb l_nm'),upper('&tbl_owner'))
from
sys.dual;

HTH -- Mark D Powell --

On Sep 23, 9:39*pm, yf... (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones)
wrote:
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('&tb l_nm'),upper('&tbl_owner'))
from
sys.dual;

HTH -- Mark D Powell --

On Sep 23, 9:39*pm, yf... (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones)
wrote:
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('&tb l_nm'),upper('&tbl_owner'))
from
sys.dual;

HTH -- Mark D Powell --

Malcolm Dew-Jones <yf110 (AT) vtn1 (DOT) victoria.tc.ca> wrote:
This is what I needed... thanks!!

--
Mark Harrison
Pixar Animation Studios