Oracle:: Drop all tables in a single shot

If you are into software development, you may have wanted to drop all tables of an Oracle database schema in a single shot. How do you do it? Here’s how I do it with a simple PL/SQL procedure.

begin
for i in (select * from tabs) loop
execute immediate ('drop table ' || i.table_name || ' cascade constraints');
end loop;
end;
/

‘tabs’ is actually an in-built view in Oracle. As explained in the follwing forum post, this PL/SQL block affects only the logged in user’s (schema) tables only.
https://community.oracle.com/message/10359255#10359255

Advertisements

6 thoughts on “Oracle:: Drop all tables in a single shot

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s