Drop all tables in Oracle DB (scheme)

Submitted by Jochus on Mon, 10/05/2010 - 23:47 | Posted in: Database

Imagine you have a list of tables in your Oracle DB and you want to drop them all using a client like SQLDeveloper. That's easier said then done, as you have to drop each table individually.

Therefore, I wrote a SQL query that generates a "DROP-sql-script":

SELECT 'DROP TABLE "' || TABLE_NAME || '" CASCADE CONSTRAINTS;' FROM user_tables;

  • user_tables is a system table which contains all the tables of the user
  • the SELECT clause will generate a DROP statement for every table

This will generate something like:

DROP TABLE "FOO" CASCADE CONSTRAINTS;                    
DROP TABLE "BAR" CASCADE CONSTRAINTS;         
DROP TABLE "DUMMY" CASCADE CONSTRAINTS;         
...

Now you can easily run that script :-)

Comments

Submitted by Ali (not verified) on Fri, 07/09/2012 - 18:01
 

Thanks, this helped a lot!

Submitted by Maria (not verified) on Tue, 11/09/2012 - 12:25
 

This was a lifesaver as DBA was not around and needed to do this urgently to get app back up and running, it worked perfectly

Submitted by Ste (not verified) on Wed, 21/11/2012 - 11:07
 

Thank you!

Submitted by Anonymous (not verified) on Wed, 23/01/2013 - 12:23
 

thanks very good

Submitted by Anonymous (not verified) on Wed, 20/02/2013 - 11:56
 

thanx

Submitted by Anonymous (not verified) on Tue, 09/07/2013 - 11:08
 

Thank you,

in my case i don't have all the table names in a table. do u have any script that removes table without mentioning them in another table.

Submitted by Jochus on Wed, 10/07/2013 - 08:12
 

Dear,

user_tables is system table, it's not a table which I created. Try to select from user_tables, you will have it as well.

Kind regards,
Jochen

Submitted by Anonymous (not verified) on Mon, 25/11/2013 - 07:00
 

You are god!, Thanks

Submitted by irshad (not verified) on Wed, 13/05/2015 - 22:32
 

Thanks , But what i would write in place of table_name if i want to drop more than one table

Submitted by Jochus on Fri, 22/05/2015 - 09:29
 

Hi irshad,

The SELECT statement will generate DROP statements for each table. table_name is variable, the value is retrieved from the SELECT query.

Jochen

Submitted by Anonymous (not verified) on Thu, 10/09/2015 - 05:09
 

I LOVE YOUR CODE IT HELPED ME A LOT <3

Submitted by Anonymous (not verified) on Fri, 04/03/2016 - 07:11
 

thanks helped a lot

Submitted by Anonymous (not verified) on Fri, 15/04/2016 - 15:34
 

i copied and pasted the script you provided and a list of the tables came up..what should i do next to delete them

Submitted by Jochus on Tue, 19/07/2016 - 10:43
 

In reply to by Anonymous (not verified)

Select all the text and copy/paste it in a new query window

Submitted by sini (not verified) on Sat, 06/05/2017 - 17:19
 

I tried using the above command
SELECT 'DROP TABLE "' || table_name || '" CASCADE CONSTRAINTS;' FROM user_tables;

its giving below error...pls let me know how to del all tables

ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
00604. 00000 - "error occurred at recursive SQL level %s"
*Cause: An error occurred while processing a recursive SQL statement
(a statement applying to internal dictionary tables).
*Action: If the situation described in the next error on the stack
can be corrected, do so; otherwise contact Oracle Support.
Error at Line: 1 Column: 70

Submitted by Jochus on Tue, 23/05/2017 - 23:18
 

In reply to by sini (not verified)

Are you sure the 'quotes' are placed correct?

Add new comment

The content of this field is kept private and will not be shown publicly.

Full HTML

  • Lines and paragraphs break automatically.
  • You can caption images (data-caption="Text"), but also videos, blockquotes, and so on.
  • Web page addresses and email addresses turn into links automatically.
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <bash>, <cpp>, <css>, <html5>, <java>, <php>, <sql>, <xml>. The supported tag styles are: <foo>, [foo].