How to drop all synonyms in oracle

I'll just repeat what I learned since no one tells how the deletion works. I came to learn about how to delete one function. I'm trying to use a procedure no parameters to drop all of the user-created database objects located within the schema from where the procedure is launched, but I'm really not sure on how to go about this. But Vijayan Srinivasan's version is not correct, because dependent objects of type 'TYPE' sometime generates errors during drop them: Are you not shooting yourself?

Martin Brambley.

Oracle DROP SYNONYM

Thanks Martin Brambley and Vijayan Srinivasan! Dropping the user isn't an option right now. Unless the user has hard to reapply permissions, its probably easier to just drop the user and recreate them.

By using our site, you acknowledge that you have read and understand our Cookie Policy , Privacy Policy , and our Terms of Service. Here's what I have so far, but I think I'm going about this the wrong way. Email Required, but never shown.

DROP SYNONYM

Up to version 10g at least you couldn't even drop the containing schema without putting the database in a special mode when this situation existed. Here is the rest: Although you can drop the Oracle-generated tables for queue support with a regular DROP TABLE, you will find yourself in the catch-22 position of then not being able to drop the related queues nor add them back. Thanks Martin Brambley, I feel we can simplify your answer in the following way.

Stack Overflow works best with JavaScript enabled.

how to drop all synonyms in oracle

Related 1006. But Vijayan Srinivasan's version is not correct, because dependent objects of type 'TYPE' sometime generates errors during drop them:. Remember that you'll probably be generating the cursor list in an order that doesn't consider dependencies that will block the drop.

You have a cursor AND a select statement.

how to drop all synonyms in oracle

Lastly, this is obviously a very dangerous method so you may want to consider putting it in a script instead of a stored procedure so it isn't left in the database for anyone to run. What you've got is a good start.

Oracle SQL: the Essential Reference by David C. Kreines

Sign up using Email and Password. NMan NMan 233 2 5 10.

how to drop all synonyms in oracle

Hot Network Questions. Also on the subject of dependencies, it is probably best to drop tables first add a DECODE in your cursor that assigns a lower numeric value to this object type and order the cursor select by this value.

how to drop all synonyms in oracle