The command that could copy the table structure, all constraints and priviledge in Oracle

Question

I am new to oracle. I would like to ask if there exist one single command that could copy table a to table b such that table b would have the same data, same structure and same access priviledge as table a? I would like to make a duplicate of a table which contain the same behavior.

See http://stackoverflow.com/q/28314804/3989608 – Lalit Kumar B Oct 19 '15 at 06:09 — Lalit Kumar B, Oct 19 '15 at 06:09

score 2 · Answer 1 · edited May 23 '17 at 11:58

Someone please correct me if I am wrong, but I don't think you can copy it with privileges/indexes as it is. That might be becasuse you need to give a new name for the index,primary key etc, and the database will not know what name needs to be given to these. So you can do this.

Run this to get the DDL of the table you want and then replace it with new table name. (my source table is TZ_TEST and I will create TZ_TEST_NEW. (Thanks to this answer for get_ddl command)
```
select replace(
    (SELECT dbms_metadata.get_ddl( 'TABLE', 'TZ_TEST' ) FROM DUAL),
    'TZ_TEST',
    'TZ_TEST_NEW') 
```
from dual
Execute the DDL

Use this to get grant permissions

select replace(
(select DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT','TZ_TEST') FROM DUAL),
'TZ_TEST',
'TZ_TEST_NEW') from dual

Similarly use DBMS_METADATA to get constraints/index etc. Execute these statmetns.

Insert data

insert into TZ_TEST_NEW
select * from TZ_TEST

Please remember that if you have an auto generated primary key, then while inserting data, you need to exclude that column from insert and select statments.

Anyone please feel free to add if I missed something.

Also we can create a procedure which can so all this but you need to be careful with all the steps. So once you do it couple of times and it works, we can create a procedure for it.

You can use DBMS_METADATA.GET_DEPENDENT_DDL to get other things like constraints, indexes, and privileges. — Jon Heller, Oct 19 '15 at 04:50
I would rather do a CTAS first and then apply the constraints and indexes. Insert would be much slower than CTAS. — Lalit Kumar B, Oct 19 '15 at 06:36

score 0 · Answer 2 · answered Oct 19 '15 at 04:58

If you are using TOAD for Oracle, then select the table name and press F4. Then select script tab in the describe window.

This will generate the table script. You just need to use Search/Replace to change the table name and execute the script.

The newly created table will contain the same behavior.

score 0 · Answer 3 · answered Oct 19 '15 at 06:35

I would do it in two steps:

Use CTAS i.e. create table as select .. to first create a copy of the table with new name with the data. You could also use PARALLEL and NOLOGGING feature to increase the performance.

For example,

create table t parallel 4 nologging as select * from emp;

Get the associated structures like indexes, constraints etc. using DBMS_METADATA.GET_DEPENDENT_DDL and execute it. But, you need to first replace the table_name to your new table_name as you have created in step 1.

CTAS would be much faster than traditional insert.

The command that could copy the table structure, all constraints and priviledge in Oracle

3 Answers3