An easy way to drop all existing objects from your Oracle Database..

If you are working with Oracle databases, there is a possibility that you come across this requirement where you need to drop all the existing objects such as Tables, Indexes, Triggers, etc. from your database. This is not going to be a big deal if you have system level privileges to the database as you can simply drop the user and then add him again so that it will simply do the trick. But if you are an ordinary user the aforementioned task often becomes tedious as you might want to drop those items one by one executing the appropriate queries.

But the following script makes the task easier as it produces a set of drop statements that can be used to drop all the objects existing in your database all at once. What you have to do is, simply run the following script using the console or your favourite query executer client(eg: executequery)and execute the drop statements produced by the script.

select 'drop '||object_type||' '|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';') from user_objects

After executing all the drop statements produced by the above script, just double check whether all the objects have been successfully dropped, by executing the following simple query.

select * from user_objects;