PL/SQL Code to Delete all Database object at the same time.

 

[Note: Before Executing this code taking a backup of the entire database is mandatory.]

CODE

      
    DECLARE
    stringa   VARCHAR2 (100);
    CURSOR cur IS SELECT * FROM user_objects;
BEGIN
    FOR c IN cur
    LOOP
        BEGIN
            stringa := '';
            IF c.object_type = 'VIEW'
            THEN
                stringa := 'drop view ' || c.object_name;
                EXECUTE IMMEDIATE stringa;
            ELSIF c.object_type = 'TABLE'
            THEN
                stringa :=
                    'drop table ' || c.object_name || ' cascade constraints';
                EXECUTE IMMEDIATE stringa;
            ELSIF c.object_type = 'SEQUENCE'
            THEN
                stringa := 'drop sequence ' || c.object_name;
                EXECUTE IMMEDIATE stringa;
            ELSIF c.object_type = 'PACKAGE'
            THEN
                stringa := 'drop package ' || c.object_name;
                EXECUTE IMMEDIATE stringa;
            ELSIF c.object_type = 'TRIGGER'
            THEN
                stringa := 'drop trigger ' || c.object_name;
                EXECUTE IMMEDIATE stringa;
            ELSIF c.object_type = 'PROCEDURE'
            THEN
                stringa := 'drop procedure ' || c.object_name;
                EXECUTE IMMEDIATE stringa;
            ELSIF c.object_type = 'FUNCTION'
            THEN
                stringa := 'drop function ' || c.object_name;
                EXECUTE IMMEDIATE stringa;
            ELSIF c.object_type = 'SYNONYM'
            THEN
                stringa := 'drop synonym ' || c.object_name;
                EXECUTE IMMEDIATE stringa;
            ELSIF c.object_type = 'INDEX'
            THEN
                stringa := 'drop index ' || c.object_name;
                EXECUTE IMMEDIATE stringa;
            ELSIF c.object_type = 'PACKAGE BODY'
            THEN
                stringa := 'drop PACKAGE BODY ' || c.object_name;
                EXECUTE IMMEDIATE stringa;
            ELSIF c.object_type = 'DATABASE LINK'
            THEN
                stringa := 'drop database link ' || c.object_name;
                EXECUTE IMMEDIATE stringa;
            END IF;
        EXCEPTION
            WHEN OTHERS
            THEN
                NULL;
        END;
    END LOOP;
END;