Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Drop all trigger in Oracle database

Posted on 2006-07-12
4
Medium Priority
?
1,513 Views
Last Modified: 2008-01-09
Hi, anyone can provide met he script to drop all trigger in oracle database ? TQ
0
Comment
Question by:netcool
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 total points
ID: 17097239
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
 
LVL 48

Expert Comment

by:schwertner
ID: 17097263
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
 
LVL 3

Expert Comment

by:haidersyed
ID: 17098172

--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
 
LVL 6

Expert Comment

by:JJSmith
ID: 17112347


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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question