Home » SQL & PL/SQL » SQL & PL/SQL » drop all objects in a schema (Oracle 11g)
drop all objects in a schema [message #400399] Tue, 28 April 2009 01:16 Go to next message
an_sush
Messages: 6
Registered: March 2009
Junior Member
Hi All,
I want a query to drop all the objects(tables, views, function, triggers etc) created by a user. Please guide me how to do the same. I just know one user/password for the database and it doesn't have SYSDBA rights. I want to clear all that this user had created.
Re: drop all objects in a schema [message #400403 is a reply to message #400399] Tue, 28 April 2009 01:30 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
so logon as this user & drop all objects.
Re: drop all objects in a schema [message #400404 is a reply to message #400399] Tue, 28 April 2009 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Fastest way:
drop user XXX cascade;

Regards
Michel
Re: drop all objects in a schema [message #400407 is a reply to message #400404] Tue, 28 April 2009 01:38 Go to previous messageGo to next message
an_sush
Messages: 6
Registered: March 2009
Junior Member
Michel Cadot wrote on Tue, 28 April 2009 12:02
Fastest way:
drop user XXX cascade;

Regards
Michel



I don't have sysdba rights and i want the user/schema after clearing all the objects.
Re: drop all objects in a schema [message #400412 is a reply to message #400407] Tue, 28 April 2009 01:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Another option is to actually search this forum for what you put in your title.
Re: drop all objects in a schema [message #400422 is a reply to message #400412] Tue, 28 April 2009 02:25 Go to previous message
elaiyavel
Messages: 114
Registered: April 2008
Senior Member
Try this option

select * from all_objects
where owner='XXXX';

Previous Topic: Doubts on Normalisation
Next Topic: error in adding partition
Goto Forum:
  


Current Time: Thu Dec 08 12:21:32 CST 2016

Total time taken to generate the page: 0.11465 seconds