Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1570
  • Last Modified:

Drop all trigger in Oracle database

Hi, anyone can provide met he script to drop all trigger in oracle database ? TQ
0
netcool
Asked:
netcool
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
run this query to generate the script for dropping all triggers for a given schema
select 'drop trigger ' || t.owner || '.' || t.trigger_name || ';' from dba_triggers where t.owner = 'schema_name';
0
 
schwertnerCommented:
use the view  dba_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.
0
 
haidersyedCommented:

--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_name;
END LOOP;
END;
0
 
JJSmithCommented:


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!!





0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now