netcool
asked on
Drop all trigger in Oracle database
Hi, anyone can provide met he script to drop all trigger in oracle database ? TQ
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
--To drop all triggers other then sys and system schema
begin
for i in (select trigger_name,owner from dba_triggers where owner not in ('SYS','SYSTEM')) LOOP
execute immediate 'DROP TRIGGER '||i.owner||'.'||i.trigger
END LOOP;
END;
mmmmnnnnn..........
should someone who does not know how to identify/drop triggers in a database; be dropping all triggers in a database?
normal schemas' can own system triggers!!
it is bad idea to drop ALL triggers.
Try to drop the triggers in an schema.
the idea is to define a cursor
select owner, triggername from dba_triggers where owner = 'SCOTT';
After that in a loop create a string
v_str := 'DROP TRIGGER ' || cursor_name.owner || '.' || cursor_name.triggername
execute immediate v_str;
I think you will learn many things if you try to do this.
But be carefull, do it on experimental instance, not on production.