0 
0 While browsing through the OTN Forum on SQL and PL/SQL, I found a question that asked ‘I can copy a table using the create table as select * from src_tbl where 1=2; is there a similar statement to copy indexes, triggers and constraints?’ Good question I would say. And my initial response was that no, there is not such a readily available mechanism. However, on second thoughts, the dbms_metadata package with its get_ddl function should be able to get us going in the right direction.
I just spent a little time on it, to prove my point. I worked out the code for triggers:
So here is the code to copy triggers between the source and the target table:
declare
p_src_tbl varchar2(30):= 'PERSONS';
p_trg_tbl varchar2(30):= 'PSN2';
l_ddl varchar2(32000);
begin
execute immediate 'create table '||p_trg_tbl||' as select * from '||p_src_tbl||' where 1=2';
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;
end;
/
Note that instead of the first line used for copying the Table Definition, we could also have use dbms_metadata.get_dll. That would optionally have included all constraint definitions. We would need to rename those constraints in order to prevent name clashes. For indexes we can use a similar approach as for triggers. Constraints are either processed along with the table or on their own. However, for some reason I could not get dbms_metadata.get_ddl or get_dependent_ddl to process my Foreign Keys correctly.
Thanks buddy. Got it rightaway.