Home » SQL & PL/SQL » SQL & PL/SQL » Drop all table under a user without dropping user (oracle 10g, win2k3)
Drop all table under a user without dropping user [message #423884] Tue, 29 September 2009 00:59 Go to next message
hussain_dilshad
Messages: 11
Registered: August 2009
Location: agra
Junior Member
I want to drop all tables under a user without dropping user. pls tell me the command.

Regards
Dilshad Hussain
Re: Drop all table under a user without dropping user [message #423886 is a reply to message #423884] Tue, 29 September 2009 01:03 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
FOR V_CUR IN (SELECT TABLE_NAME FROM USER_TABLES)
LOOP
    EXECUTE IMMEDIATE
      'DROP TABLE ' || V_CUR.TABLE_NAME || ' CASCADE CONSTRAINTS PURGE';
END LOOP;


regards,
Delna
Re: Drop all table under a user without dropping user [message #423887 is a reply to message #423884] Tue, 29 September 2009 01:05 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no command for this.
You have to drop all tables one by one.

I once posted a script for this, see http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:57926815441661#66700686444171

Regards
Michel
Previous Topic: Variables in SQL Query
Next Topic: decode function help
Goto Forum:
  


Current Time: Sun Dec 04 04:57:11 CST 2016

Total time taken to generate the page: 0.24503 seconds