How to drop all tables in Oracle

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




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