Oracle Drop All Tables, Views, Sequences, etc.

oracle-drop-all-tables.sql

/**
 * USE WITH CAUTION!
 * This script attempts to drop every object it can find in the current schema.
 * - Tables (including constraints)
 * - Views
 * - Sequences
 * - More...
 *
 * @author John A. Marsh - ThreeLeaf.com (c) 2014
 */

--/
SET SERVEROUTPUT ON;

DECLARE
    CURSOR REF_CONSTRAINTS IS
        SELECT
            CONSTRAINT_NAME,
            TABLE_NAME
        FROM
            USER_CONSTRAINTS
        WHERE
            CONSTRAINT_TYPE = 'R'
        ORDER BY
            CONSTRAINT_NAME
    ;
    CURSOR USERTABLES IS
        SELECT
            TABLE_NAME
        FROM
            USER_TABLES
        ORDER BY
            TABLE_NAME
    ;
    CURSOR MATERIALIZED_VIEWS IS
        SELECT
            MVIEW_NAME
        FROM
            USER_MVIEWS
        ORDER BY
            MVIEW_NAME
    ;
    CURSOR OBJECTS IS
        SELECT
            OBJECT_NAME,
            OBJECT_TYPE
        FROM
            USER_OBJECTS
        ORDER BY
            OBJECT_NAME,
            OBJECT_TYPE
    ;
BEGIN
    -- DISABLE FOREIGN KEY CONSTRAINTS
    DBMS_OUTPUT.PUT_LINE('DROPPING CONSTRAINTS:');
    FOR C IN REF_CONSTRAINTS
    LOOP
        DBMS_OUTPUT.PUT_LINE('DROPPING CONSTRAINT ' || RPAD(C.CONSTRAINT_NAME, 18, ' ') || ' ON TABLE ' || C.TABLE_NAME);
        BEGIN
          EXECUTE IMMEDIATE 'ALTER TABLE ' || C.TABLE_NAME || ' DROP CONSTRAINT ' || C.CONSTRAINT_NAME;
        EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('** EXCEPTION DROPPING CONSTRAINT ' || C.CONSTRAINT_NAME || ' ON TABLE ' || C.TABLE_NAME || '; ERROR:' || SQLERRM);
        END;
    END LOOP;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('DROPPING MATERIALIZED VIEWS:');
    FOR MV IN MATERIALIZED_VIEWS
    LOOP
        DBMS_OUTPUT.PUT_LINE('DROPPING MVIEW      ' || MV.MVIEW_NAME);
        BEGIN
            EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW ' || MV.MVIEW_NAME || ' PRESERVE TABLE';
        EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('** EXCEPTION DROPPING MATERIALIZED VIEW ' || MV.MVIEW_NAME || '; ERROR:' || SQLERRM);
        END;
    END LOOP;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('DROPPING TABLES:');
    FOR T IN USERTABLES
    LOOP
        DBMS_OUTPUT.PUT_LINE('DROPPING TABLE      ' || T.TABLE_NAME);
        BEGIN
            EXECUTE IMMEDIATE 'DROP TABLE ' || T.TABLE_NAME || ' PURGE';
        EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('** EXCEPTION DROPPING TABLE ' || T.TABLE_NAME || '; ERROR:' || SQLERRM);
        END;
    END LOOP;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('DROPPING OTHER OBJECTS:');
    FOR O IN OBJECTS
    LOOP
      DBMS_OUTPUT.PUT_LINE('DROPPING ' || RPAD(O.OBJECT_TYPE, 10, ' ') || ' ' || O.OBJECT_NAME);
      BEGIN
        EXECUTE IMMEDIATE 'DROP ' || O.OBJECT_TYPE || ' "' || O.OBJECT_NAME || '"';
      EXCEPTION WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('** EXCEPTION DROPPING ' || RPAD(O.OBJECT_TYPE, 10, ' ') || ' ' || O.OBJECT_NAME || '; ERROR:' || SQLERRM);
      END;
    END LOOP;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('PURGING RECYCLE BIN');
    EXECUTE IMMEDIATE 'PURGE RECYCLEBIN';
END;
/