Solved

how to drop all the triggers and sequences in a schema

Posted on 2009-04-03
9
1,514 Views
Last Modified: 2013-12-18
Hi,

I have many triggers and sequences in my schema. I would like to drop all the triggers, sequences. What is the command i have to use to drop all sequences and triggers. Any code sample, links, ideas, resources highly appreciated. Thanks in advance.
0
Comment
Question by:gudii9
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 14

Accepted Solution

by:
ajexpert earned 250 total points
ID: 24065873
The following code should help you.  You have to pass the schema name.
My advice is to take a backup before executing this proceudre.
 

CREATE OR REPLACE PROCEDURE PR_DROP_SEQUENCE_TRIGGER
AS
CURSOR C1 IS
SELECT * FROM ALL_OBJECTS AO
WHERE OBJECT_TYPE 
IN ('SEQUENCE', 'TRIGGER')
AND AO.OWNER = <schemaname>
ORDER BY OBJECT_TYPE;
 
 
BEGIN
 
FOR c1_rec IN C1
 
LOOP
   IF c1_rec.OBJECT_TYPE = 'SEQUENCE' THEN
      EXECUTE IMMEDIATE ' DROP SEQUENCE ' || c1_rec.object_name;
   ELSIF c1_rec.OBJECT_TYPE = 'TRIGGER' THEN
      EXECUTE IMMEDIATE ' DROP TRIGGER ' ||c1_rec.object_name;
   END IF;
 
END LOOP;
 
END;

Open in new window

0
 
LVL 17

Assisted Solution

by:k_murli_krishna
k_murli_krishna earned 100 total points
ID: 24065896
You can also generate a drop script using:

SELECT 'DROP TRIGGER ' || OWNER || '.' || OBJECT_NAME || ';' FROM ALL_OBJECTS AO
WHERE OBJECT_TYPE = 'TRIGGER' AND OWNER = <schemaname> ORDER BY OBJECT_NAME;

Similarly,
SELECT 'DROP SEQUENCE ' || OWNER || '.' || OBJECT_NAME || ';' FROM ALL_OBJECTS AO
WHERE OBJECT_TYPE = 'SEQUENCE' AND OWNER = <schemaname> ORDER BY OBJECT_NAME;
Here also you have to pass the schema name.
You can redirect output of both SELECT's to an .sql file and run them separately at SQL prompt
sql> connect scott/tiger@testdb
sql> spool <path>\log.txt
sql> @ <path>\drop_triggers.sql
sql> @ <path>\drop_sequences.sql
sql> spool off
0
 
LVL 7

Author Comment

by:gudii9
ID: 24065947
As in the post 24065873. Do I need to run that procedure from the super user like 'system'. Or can i execute from the same schema where the triggers, sequences are present since I gave all privileges with admin option to that particular schema. Please advise
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Author Comment

by:gudii9
ID: 24065995
As in the post 24065873 when I try to run from same schema it did not drop the triggers, sequences. In 'system' user  when i compile the procedure it says compiled. But I could not find it under procedures to run or execute. Please advise.
0
 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 24066845
In Cursor SELECT instead of SELECT *, use OBJECT_NAME, OBJECT_TYPE. You need to create the procedure in a user i.e. schema instead of connecting using system and creating it as it is i.e.
CREATE OR REPLACE PROCEDURE SCHEMA_NAME.PR_DROP_SEQUENCE_TRIGGER

Go through following references for more details:
http://www.experts-exchange.com/Database/Oracle/Q_21747080.html
http://forums.oracle.com/forums/thread.jspa?messageID=1101028
http://snipplr.com/view/3776/list-all-stored-procedures-in-an-oracle-database/

Also, as one more/last option, check the syntax for CREATE OR REPLACE PROCEDURE from ajexpert. I am not doubting it but just in case there is some slip.
0
 
LVL 7

Assisted Solution

by:Fayyaz
Fayyaz earned 100 total points
ID: 24072127
Let say you want to drop the triggers and sequences for schema 'GUDII9' then
SQL> conn system/manager
SQL> spool drop_trig.sql
SELECT 'DROP TRIGGER ' || OWNER || '.' || OBJECT_NAME || ';' FROM DBA_OBJECTS
WHERE OBJECT_TYPE = 'TRIGGER' AND OWNER = 'GUDII9' ;
SQL> spool off;
SQL> @drop_trig.sql
same is the case for sequence drop.

SQL> conn system/manager
SQL> spool drop_seq.sql
SELECT 'DROP SEQUENCE  ' || OWNER || '.' || OBJECT_NAME || ';' FROM DBA_OBJECTS
WHERE OBJECT_TYPE = 'SEQUENCE' AND OWNER = 'GUDII9' ;
SQL> spool off;
SQL> @drop_seq.sql

 
0
 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 24074052
Fayyaz, I missed something and you missed the complimentary.
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 50 total points
ID: 24077187
See attached.
drop-objects.txt
0
 
LVL 7

Author Closing Comment

by:gudii9
ID: 31566512
Thank you very much for your help. I appreciate it
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

707 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