Oracle SQL script to drop the entire schema content. This is very useful right before an import (restore).

OracleDropSchemaContent.SQL

SET SERVEROUTPUT ON
exec dbms_output.enable(1000000);
DECLARE
    v_typ          VARCHAR2(32);
    v_name         VARCHAR2(32);
    v_constraint   VARCHAR2(32);
    v_sql          VARCHAR2(100);

    CURSOR c_objekte IS
        SELECT   typ, NAME
        FROM     (select 91 as seq, 'INDEX' as typ, OBJECT_NAME as name from user_objects WHERE OBJECT_TYPE='INDEX' AND not(OBJECT_NAME like 'BIN$%') AND (Object_NAME like 'I_%' OR Object_NAME like 'FK_%')
                    UNION
                    select 92 as seq, 'INDEX' as typ, OBJECT_NAME as name from user_objects WHERE OBJECT_TYPE='INDEX' AND not(OBJECT_NAME like 'BIN$%')  
                    AND ( Object_NAME like 'UK_%' OR Object_NAME like 'UX_%' OR Object_NAME like '%_UK1')
                    UNION
                    select 93 as seq, 'INDEX' as typ, OBJECT_NAME as name from user_objects WHERE OBJECT_TYPE='INDEX' AND not(OBJECT_NAME like 'BIN$%')
                    UNION
                    select 20 as seq, 'SEQUENCE' as typ, OBJECT_NAME as name from user_objects WHERE OBJECT_TYPE='SEQUENCE' AND not(OBJECT_NAME like 'BIN$%')
                    UNION
                    select 30 as seq, 'PACKAGE' as typ, OBJECT_NAME as name from user_objects WHERE OBJECT_TYPE='PACKAGE' AND not(OBJECT_NAME like 'BIN$%')
                    UNION
                    select 40 as seq, 'PROCEDURE' as typ, OBJECT_NAME as name from user_objects WHERE OBJECT_TYPE='PROCEDURE' AND not(OBJECT_NAME like 'BIN$%')
                    UNION
                    select 50 as seq, 'FUNCTION' as typ, OBJECT_NAME as name from user_objects WHERE OBJECT_TYPE='FUNCTION' AND not(OBJECT_NAME like 'BIN$%')
                    UNION
                    select 60 as seq, 'TABLE' as typ, OBJECT_NAME as name from user_objects WHERE OBJECT_TYPE='TABLE' AND not(OBJECT_NAME like 'BIN$%')
                    UNION
                    select 55 as seq, 'VIEW' as typ, OBJECT_NAME as name from user_objects WHERE OBJECT_TYPE='VIEW' AND not(OBJECT_NAME like 'BIN$%')
                  UNION
                  SELECT 1 AS seq, 'materialized view' AS typ, name AS NAME
                  FROM   user_snapshots
                  UNION
                  SELECT 6 as seq, 'type' as typ, TYPE_NAME as NAME 
                  FROM user_types WHERE TYPECODE <> 'OBJECT'
                  UNION
                  SELECT 8 as seq, 'type' as typ, TYPE_NAME as NAME 
                  FROM user_types WHERE TYPECODE = 'OBJECT'
                  )
        ORDER BY seq;
        
    CURSOR c_objekte2 IS
        SELECT   typ, NAME
        FROM     (SELECT 3 AS seq, 'sequence' AS typ, sequence_name AS NAME
                  FROM   user_sequences WHERE sequence_name='Y')
        ORDER BY seq;

    CURSOR c_constraints IS
        SELECT table_name, constraint_name
        FROM   user_constraints
        WHERE  constraint_type = 'R';
BEGIN
    OPEN c_constraints;

    LOOP
	    BEGIN
			FETCH c_constraints
			INTO  v_name, v_constraint;

			EXIT WHEN c_constraints%NOTFOUND;
			v_sql := 'alter table ' || v_name || ' drop constraint ' || v_constraint;
			DBMS_OUTPUT.put_line(v_sql);

			EXECUTE IMMEDIATE v_sql;
		EXCEPTION
			WHEN OTHERS THEN  
			DBMS_OUTPUT.put_line('Error in exec ' || v_sql);
		END;
    END LOOP;

    CLOSE c_constraints;

    OPEN c_objekte;

    LOOP
		BEGIN
			FETCH c_objekte
			INTO  v_typ, v_name;

			EXIT WHEN c_objekte%NOTFOUND;
			v_sql := 'drop ' || v_typ || ' ' || v_name;
			DBMS_OUTPUT.put_line(v_sql);

			EXECUTE IMMEDIATE v_sql;
		EXCEPTION
			WHEN OTHERS THEN  
			DBMS_OUTPUT.put_line('Error in exec ' || v_sql);
		END;
    END LOOP;

    CLOSE c_objekte;
    

    OPEN c_objekte2;

    LOOP
		BEGIN
			FETCH c_objekte2
			INTO  v_typ, v_name;

			EXIT WHEN c_objekte2%NOTFOUND;
			v_sql := 'drop ' || v_typ || ' ' || v_name;
			DBMS_OUTPUT.put_line(v_sql);

			EXECUTE IMMEDIATE v_sql;
		EXCEPTION
			WHEN OTHERS THEN  
			DBMS_OUTPUT.put_line('Error in exec ' || v_sql);
		END;
    END LOOP;
DBMS_OUTPUT.put_line('running');
CLOSE c_objekte2;

    v_sql := 'PURGE RECYCLEBIN';
    DBMS_OUTPUT.put_line(v_sql);

    EXECUTE IMMEDIATE v_sql;
    
END;
/

EXIT;