Monday, September 19, 2011

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 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.

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    :-) 


Anonymous said...

Why not use
drop user cascade

Why this cumbersome method?


This is because if you don't have the privlege of "drop user" then how you will remove the object of schemas??

I have already mention when to use this method. :)


chandu reddy said...

@anonymous :
if we do srop user cascade it deletes entire schema along with the objects but sometimes we need to delete only objects but not schema then in this situations we go for a script.
follow the link for the script :

chandu reddy said...

Hi neeraj, is their any script to delete all schemas ina db except default schema's?

send me the script to

thanks in advance


Hi Chandu

Checj your mail.

Anonymous said...

I like the helpful info you provide in your articles.
I'll bookmark your weblog and check again here frequently.
I am quite certain I will learn plenty of new stuff right here!
Best of luck for the next!

my web-site - spekulant

Imran Ahmed said...

Hi Neeraj,
I am trying to drop all the schema objecs at once,but it is taking long time to drop all the schema objects and the schema size 178GB.

------------------------------ ------------------- ---------- ----------
test1 FUNCTION 30 0
test1 INDEX 1210 0
test1 LOB 21 0
test1 PACKAGE 48 0
test1 PACKAGE BODY 37 11
test1 PROCEDURE 2 1
test1 SYNONYM 7 0
test1 TABLE 409 0
test1 TRIGGER 389 4
test1 VIEW 377 2
I am using the below command to clean up the schema
exec sys.dbutils.drop_schema(ps_schema=>'TEST1');

Let me know is there any way to calculate the time required to cleanup the schema?