Clear Oracle Schema all object (Be careful)

SET SERVEROUTPUT ON

DECLARE

  l_count    NUMBER;

  l_cascade  VARCHAR2(20);

BEGIN

  << dependency_failure_loop >>

  FOR i IN 1 .. 5 LOOP

    EXIT dependency_failure_loop WHEN l_count = 0;

    l_count := 0;

    

    FOR cur_rec IN (SELECT object_name, object_type 

                    FROM   user_objects) LOOP

      BEGIN

        l_count := l_count + 1;

        l_cascade := NULL;

        IF cur_rec.object_type = 'TABLE' THEN

          l_cascade := ' CASCADE CONSTRAINTS';

        END IF;

        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"' || l_cascade;

      EXCEPTION

        WHEN OTHERS THEN

          NULL;

      END;

    END LOOP;

    -- Comment out the following line if you are pre-10g, or want to preserve the recyclebin contents. 

    EXECUTE IMMEDIATE 'PURGE RECYCLEBIN';

    DBMS_OUTPUT.put_line('Pass: ' || i || '  Drops: ' || l_count);

  END LOOP;

END;

/

set feedback off

set heading off

set termout off

set linesize 1000

set trimspool on

set verify off

spool c:\temp\drop_objects.lst

SELECT 'DROP ' || OBJECT_TYPE || ' ' || OBJECT_NAME ||';'   FROM USER_OBJECTS

WHERE OBJECT_TYPE <> 'TABLE' AND OBJECT_TYPE <> 'INDEX' AND OBJECT_TYPE<>'PACKAGE BODY' AND OBJECT_TYPE<>'TRIGGER'  AND OBJECT_TYPE<>'LOB'

UNION ALL

SELECT 'DROP ' || OBJECT_TYPE || ' ' || OBJECT_NAME ||' CASCADE CONSTRAINTS;'   FROM USER_OBJECTS

WHERE OBJECT_TYPE = 'TABLE';

spool off;

@c:\temp\drop_objects.lst

prompt 'Purging the recycle bin...'

purge recyclebin;

host del c:\temp\drop_objects.lst