Delete all objects in an Oracle schema

oracle_delete_all_objects.sql

-- Maximum carnage. Delete everything in a schema
declare
    V_SCHEMA varchar(100);
begin
    V_SCHEMA := 'APSM';

    -- drop queues
    for R_QUEUE in (
        select V_SCHEMA || '.' || NAME as QUEUE_NAME
        from SYS.ALL_QUEUES
        where OWNER = V_SCHEMA
          and QUEUE_TYPE = 'NORMAL_QUEUE'
        )
        loop
            DBMS_OUTPUT.put_line('Removing queue: ' || R_QUEUE.QUEUE_NAME);

            DBMS_AQADM.STOP_QUEUE(R_QUEUE.QUEUE_NAME);
            DBMS_AQADM.DROP_QUEUE(R_QUEUE.QUEUE_NAME);
        end loop;

    -- drop queue tables
    for R_QUEUE_TABLE in (
        select V_SCHEMA || '.' || QUEUE_TABLE as QUEUE_TABLE
        from SYS.ALL_QUEUE_TABLES
        where OWNER = V_SCHEMA
        )
        loop
            DBMS_OUTPUT.put_line('Removing queue table: ' || R_QUEUE_TABLE.QUEUE_TABLE);

            DBMS_AQADM.DROP_QUEUE_TABLE(R_QUEUE_TABLE.QUEUE_TABLE);
        end loop;

    -- disable foreign keys
    for R_KEY in (
        select AC.TABLE_NAME
             , AC.CONSTRAINT_NAME
             , 'alter table "' || AC.OWNER || '"."' || AC.TABLE_NAME || '" drop constraint "' ||
               AC.CONSTRAINT_NAME || '"' as STATEMENT
        from SYS.ALL_CONSTRAINTS AC
        where AC.CONSTRAINT_TYPE = 'R'
          and AC.OWNER = V_SCHEMA
        )
        loop
            DBMS_OUTPUT.put_line('Dropping foreign key ' || R_KEY.CONSTRAINT_NAME || ' in ' || R_KEY.TABLE_NAME);
            execute immediate R_KEY.STATEMENT;
        end loop;

    -- drop views, packages, sequences, procedures and functions
    for R_OBJECT in (
        select OBJECT_TYPE
             , OBJECT_NAME
             , 'drop ' || OBJECT_TYPE || ' "' || OWNER || '"."' || OBJECT_NAME || '"' as STATEMENT
        from SYS.ALL_OBJECTS
        where OBJECT_TYPE in ('VIEW', 'SEQUENCE', 'PROCEDURE', 'PACKAGE', 'FUNCTION')
          and OWNER = V_SCHEMA
          and GENERATED = 'N'
        )
        loop
            DBMS_OUTPUT.put_line('Dropping ' || lower(R_OBJECT.OBJECT_TYPE) || ' ' || R_OBJECT.OBJECT_NAME);
            execute immediate R_OBJECT.STATEMENT;
        end loop;

    -- drop tables
    for R_TABLE in (
        select TABLE_NAME, 'drop table "' || OWNER || '"."' || TABLE_NAME || '"' as STATEMENT
        from SYS.ALL_TABLES
        where OWNER = V_SCHEMA
        )
        loop
            DBMS_OUTPUT.put_line('Dropping table ' || R_TABLE.TABLE_NAME);
            execute immediate R_TABLE.STATEMENT;
        end loop;

    -- drop types
    for R_TYPE in (
        select OBJECT_TYPE
             , OBJECT_NAME
             , 'drop ' || OBJECT_TYPE || ' "' || OWNER || '"."' || OBJECT_NAME || '" force' as STATEMENT
        from SYS.ALL_OBJECTS
        where OBJECT_TYPE in ('TYPE')
          and OWNER = V_SCHEMA
          and GENERATED = 'N'
        )
        loop
            DBMS_OUTPUT.put_line('Dropping ' || lower(R_TYPE.OBJECT_TYPE) || ' ' || R_TYPE.OBJECT_NAME);
            execute immediate R_TYPE.STATEMENT;
        end loop;

    DBMS_OUTPUT.put_line('Done.');
end;