Drop all object of Schemas

Sometimes we need to drop the all objects of schemas while importing. The basic approach is drop the schemas and recreate the schemas. This method is quite efficient . To perform this operation we need the system or sysdba privileges to create and drop the user. If anyone have not the system and sysdba privileges then, dropping all objects is the option . Below is the Demo where we will drop all objects of "SCOTT" schemas.

Step 1 : Generate the scripts for dropping the schemas :

SQL> spool C:\genera_dropall.sql

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

'DROP'||OBJECT_TYPE||''||OBJECT_NAME||DECODE(OBJECT_TYPE,'TABLE','CASCADECONSTRAINTS;',';')          
------------------------------------------------------------------------------------------------------------
drop TABLE SALGRADE CASCADE CONSTRAINTS;                                                                                                                                                        
drop TABLE BONUS CASCADE CONSTRAINTS;                                                                                                                                                        
drop INDEX PK_EMP;                                                                                                                                                                      
drop TABLE EMP CASCADE CONSTRAINTS;                                                                                                                                        
drop TABLE DEPT CASCADE CONSTRAINTS;                                                                                                                                                    
drop INDEX PK_DEPT;                                                                                                                                                                        
6 rows selected.
Since we have the drop script as 'genera_dropall.sql'

Step  2  :  Now we will drop all_objects i.e, 'genera_dropall.sql' script is used to drop everything in schemas.

SQL>@"genera_dropall.sql" 
Now check the object in scott schemas

SQL> select * from tab;
TNAME                                                       TABTYPE           CLUSTERID
------------------------------                               ------------             --------------
BIN$5JksbkFeSai/0JTwbJOenQ==$0           TABLE
BIN$KtthiEIaRZmkv/5+FoYu5A==$0              TABLE
BIN$L/qcqzTxTsm8XHkDrfANOg==$0          TABLE
BIN$opUCTunxRf+0AUbhOzzBgw==$0        TABLE

The SQL was written against Oracle (hence the "purge recyclebin" at the bottom and the exclusion of objects already in the recycle bin from the "drop" loop).

Step  3  : Purge the recyclebin objects.

SQL>purge recyclebin ;
Recyclebin purged.

This will produce a list of drop statements. Not all of them will execute - if we drop with cascade, dropping the PK_* indices will fail. But in the end, you will have a pretty clean schema. Confirm with: 

SQL> select * from user_objects;
no rows selected


Enjoy    :-)