Script for Dropping all objects from Schema

Guys,Use below script to drop all objects from schema but be careful when you are firing below command any schema,Check whether you are executing following script in correct schema.


Begin
For c1 in(select a.object_name object_name,a.object_type object_type from user_objects a where a.object_type not in('LOB','INDEX','TABLE PARTITION','TRIGGER','INDEX PARTITION','PACKAGE BODY','JOB',
'JAVA CLASS') And upper(object_name) = object_name)

LOOP
IF C1.object_type ='TABLE' THEN
Execute Immediate 'DROP '||C1.OBJECT_TYPE||' '||C1.object_name||' CASCADE CONSTRAINTS ';
ELSIF C1.object_type ='JAVA SOURCE' THEN
  Execute Immediate 'DROP '||C1.OBJECT_TYPE||' '||C1.object_name;
ELSIF C1.object_type ='TYPE' THEN
  Execute Immediate 'DROP '||C1.OBJECT_TYPE||' '||C1.object_name||' FORCE'; 
ELSE
Execute Immediate 'DROP '||C1.OBJECT_TYPE||' '||C1.object_name;
END IF;
End Loop;
Exception
When Others then
Dbms_Output.put_line('Msg '||SQLERRM );
End;


Don't forget to purge the recyclebin,

Execute below command:

SQL> Purge Recyclebin;

Recyclebin is same as our windows recycle bin which will store all deleted files.


If you dont want to move dropping items to recycle bin then you can make it off for particular session using below command.

SQL> ALTER SESSION SET RECYCLEBIN=OFF;

(Or ) --For On

SQL > ALTER SYSTEM SET RECYCLEBIN= ON;

Below command is for entire Database

SQL > ALTER SYSTEM SET RECYCLEBIN= OFF;

(or)

SQL > ALTER SESSION SET RECYCLEBIN= OFF;

You can cross check whether recycle bin is On/Off as of now using below query.

SQL > SELECT Value FROM V$parameter WHERE Name = 'recyclebin';

(Or) 

SQL > SHOW PARAMETER RECYCLEBIN;

and you can query recyclebin to check dropped objects.

SQL> Select * from Recyclebin;

Objecttypes which will be stored in recyclebin:

 

Before/After purging recyclebin:

And use below  program to drop Oracle JOBS from your database.