How to drop all tables in Oracle

Written by coregps on December 6th, 2006 in Oracle.

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

If you're new here, you may want to subscribe to my RSS feed. Thanks for visiting!

We sometimes need to drop all tables in Oracle. If there are many tables in the database, it will be a terrible work. The easiest way is to write a simple small SQL*Plus script to retrieve all tables of a specified user and generate DROP statements. It is something look like this:

SQL> spool c:\drop_tables.sql
SQL> SELECT ‘DROP TABLE ‘ || table_name || ‘ CASCADE CONSTRAINTS;’ FROM user_tables;
SQL> spool off

Then we can execute the generated script “drop_tables.sql” like following:

SQL> @c:\drop_tables.sql

But be careful! Be sure to log on as the correct user!

If we login as sys or system, we can run this script:

spool c:\drop_all_tables.sql
select “drop table ” || table_name || ” cascade constraints;” from all_tables where owner = ‘User Name’
spool off

Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.

• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 
• 

6 Responses to “How to drop all tables in Oracle”

1. Dirk Says:
   July 30th, 2008 at 5:11 pm

   thx, for this entry… exactly what I was looking for!

2. Kai Kai Says:
   November 12th, 2008 at 1:48 am

   Thanks for the answer, this is wath i am talking about!!!

3. Srujan Says:
   November 28th, 2008 at 6:00 pm

   Thanks a Lot.

   Thank you very much.

   This is wht i needed. Thanks a Lot.

4. gaurav sani Says:
   December 24th, 2008 at 1:29 pm

   Hi,

   Can i use this for delete as well.

   “delete from table ” || table_name || ” cascade constraints;” from all_tables where owner = ‘User Name’

   ??

5. Nitin Says:
   December 24th, 2008 at 2:40 pm

   Thanks…

6. coregps Says:
   December 25th, 2008 at 9:15 am

   Hi gaurav sani,

   Please try the following sql statement.

   select ‘truncate table ‘ || table_name || ‘;’ from sys.dba_tables where owner=’User Name’;

Leave a Reply