Drop tables for a user (A Workaround)

I am not an expert in database and hence i got stuck when I had to drop around hundreds of tables for a user in a database (Oracle 10g in my case). The tables were having complicated relationships and hence they were having dependencies on other tables maintained by constraints. But then I got a very nice trick to do the same. Below mentioned are the steps for the same:

Log on to the database as the user whose tables has to be dropped and run the following command,

select ‘drop table ‘ || tname || ‘cascade constraints;’ from tab ;

The output of this script will be the list of drop table commands which will also drop the constraints. For example if there are two tables say customer and account, the output will be like:

drop table customer cascade constraints;

drop table account cascade constraints;

Now the script for dropping all the tables is ready. This script will drop all the tables. However this script could be hand-coded but if you have hundreds of tables this trick will help you. You can also modify the command to write the output in a .sql file so that you can directly execute it.

However this is of course a workaround but this had solved my problem.

Cheers,

Kamlesh

Advertisements

One thought on “Drop tables for a user (A Workaround)

  1. You can also try this script

    SELECT ‘ DROP TABLE ‘ || OBJECT_NAME || ‘ CASCADE CONSTRAINT ; ‘ FROM USER_OBJECTS WHERE OBJECT_TYPE=’TABLE’;

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