Drop all tables for one user in Oracle database

Posted: February 03, 2014

To drop all tables for a single user in an Oracle database, use one of the following two queries. The queries return the drop statements. Execute these drop statements and you are done.

To drop all tables for the current user:

select 'drop table '||table_name||' cascade constraints;' from user_tables;

To drop all tables for another user:

select 'drop table <user_name>'||table_name||' cascade constraints;' from dba_tables WHERE owner='<user_name>';