Oracle Create Table As and Index, Triggers, Constraint recreation

create table temp2 as select * from temp1 where 1=2

above SQL will create new table temp2; having structure of temp1.
Is there any way to copy indexes, and triggers from temp1 to temp2 in similar fashion?

There is no direct way – at least that I know of. However, if you are on Oracle 9i or above, the supplied package dbms_metadata can be very helpful.

You can use dbms_metadata.ddl to extract the DDL for tables+constraints and indexes and triggers separately. After tweaking these CLOB values just a little to prevent name collisions, you can use EXECUTE IMMEDIATE in a simple PL/SQL statement to copy the objects you require.

For example to copy the triggers:

p_src_tbl varchar2(30):= ‘PERSONS’;
p_trg_tbl varchar2(30):= ‘PSN2’;
l_ddl varchar2(32000);
for trg in (select trigger_name from user_triggers where table_name = p_src_tbl) loop
l_ddl:= cast(replace(replace(dbms_metadata.get_ddl( ‘TRIGGER’, trg.trigger_name),p_src_tbl,p_trg_tbl),trg.trigger_name,substr(p_trg_tbl||trg.trigger_name, 1, 30)) as varchar2);
execute immediate substr(l_ddl, 1, instr(l_ddl,’ALTER TRIGGER’)-1);
end loop;


About this entry