Home » SQL & PL/SQL » SQL & PL/SQL » dropping the tables (oracle 10g)
dropping the tables [message #402974] Wed, 13 May 2009 05:43 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi i am having an existing database with n no of tables in it,views,triggers,procedueres,packages.now is it possible for me to drop all the tables at a single time by giving any command because if i want to restore a dmp file everytime i have to drop the user and drop the tablespace so instead of that can i try for going with dropping all the tables at a time?is there any command available?
Re: dropping the tables [message #402979 is a reply to message #402974] Wed, 13 May 2009 05:47 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
Loop over user tables and use execute immediate to drop each one in turn.
Re: dropping the tables [message #402983 is a reply to message #402979] Wed, 13 May 2009 05:51 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
is it not possible to drop all the tables in a single shot?
Re: dropping the tables [message #402984 is a reply to message #402983] Wed, 13 May 2009 05:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No, there's no commandfor that.

You can do a DROP USER < username> CASCADE; which will drop the user and everything in it.

Other than that, you'll have to do as @Cookiemonster suggests, although you'll probably want to do a DROP TABLE <table_name> CASCADE CONSTRAINTS to get rid of any FKs
Re: dropping the tables [message #402985 is a reply to message #402984] Wed, 13 May 2009 06:04 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
i think dropping an entire schema is suggestable than doing this so
right.how can we drop that much table as i am having more than 500.
if i dropping an existing user everytime i have to shutdown the database and have to give startup?unless ding so it is giving exception like "cannot drop a user that is currently connected" even though no body is connecting to it
Re: dropping the tables [message #402988 is a reply to message #402974] Wed, 13 May 2009 06:06 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
If oracle says someone is connected then I tend to believe it.
Check v$session.
Re: dropping the tables [message #402992 is a reply to message #402985] Wed, 13 May 2009 06:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Write a little pl/sql block that will loop through USER_TABLES, or ALL_TABLES for a specific owner, and do an EXECUTE IMMEDIATE to drop the table for each record in the loop.
Re: dropping the tables [message #402996 is a reply to message #402992] Wed, 13 May 2009 06:41 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi JR,
OP want to drop all the procedures, functions, and all objects created under that schema. I have not tried. will all_tables loop help?

My second doubt why loop? when you are selecting from all_tables with some conditions to delete?

yours
dr.s.raghunathan
Re: dropping the tables [message #402999 is a reply to message #402996] Wed, 13 May 2009 06:55 Go to previous message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
dr.s.raghunathan wrote on Wed, 13 May 2009 12:41
hi JR,
OP want to drop all the procedures, functions, and all objects created under that schema. I have not tried. will all_tables loop help?


No, OP mentions other object types but only specifically asked about dropping tables.
If you want to do everything you need to loop over user_objects and use more complicated code to allow for the different object types.
Or just drop the user.

Quote:

My second doubt why loop? when you are selecting from all_tables with some conditions to delete?



Because it's the easiest way to code it?
You have to issue one drop command per table - that kind of needs a loop.
The alternative is to write one select statement that outputs the full drop command for each table and spool that to a file, then run the file.

EDIT: fixed tags

[Updated on: Wed, 13 May 2009 06:56]

Report message to a moderator

Previous Topic: How to find out present text as well not present
Next Topic: Can you let me know the difference between two anonymous blocks (merged 3)
Goto Forum:
  


Current Time: Wed Dec 07 02:51:29 CST 2016

Total time taken to generate the page: 0.10467 seconds