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?

Submitted by Chris (not verified) on Wed, 18/04/2018 - 02:26
 

Very excellent, Thank you.

Submitted by Ezhil (not verified) on Tue, 07/08/2018 - 13:04
 

Thanks..... it's very helpfull to me...... Thank you so much

Submitted by Roger Florez (not verified) on Wed, 28/11/2018 - 05:41
 

Thanks heaps

Submitted by Jefro (not verified) on Thu, 10/01/2019 - 20:02
 

I had previously set up 3 databases in SQL Developer using scripts and a setup batch file provided along with the Murach PL/SQL book. Now I am taking a class and want to set up my own (empty) database. I followed instructions found on numerous sites to create a new connection and database, but the database created was auto-populated with a large number of unwanted tables (most with a $ in the name). I can drop/delete most of these tables individually, however that is slow-going and some can't be dropped (an error message appears). I tried running the provided script (edited for the auto-created table names), and it displays as if its working, but then leaves all of the tables intact.

Is there a way to force-drop all existing tables? Or better yet, is there a way to create a new database that has no auto-generated tables so the only ones in it will be the ones I create?

Thanks for any replies

Submitted by Anonymous (not verified) on Mon, 25/02/2019 - 19:28
 

it shows error for me

ORA-00904: "TABLE_NAME": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 1 Column: 25

please advise what it could be

Submitted by L (not verified) on Wed, 20/03/2019 - 20:47
 

helped, thx

Submitted by Anonymous (not verified) on Fri, 12/06/2020 - 12:38
 

Thanks a lot!

Submitted by Anonymous (not verified) on Fri, 26/06/2020 - 12:14
 

Thanks a lot. That saved me a lot of time.

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>, <javascript>, <php>, <sql>, <xml>. The supported tag styles are: <foo>, [foo].
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.