drop

 SET SERVEROUTPUT ON;
DECLARE
  v_schema VARCHAR2(30) := 'YOUR_SCHEMA_NAME'; -- Replace with your schema name
  v_count NUMBER;
  v_sql VARCHAR2(4000);
  v_start_time TIMESTAMP;
  
  -- Log table to track dropped objects
  PROCEDURE create_log_table IS
  BEGIN
    EXECUTE IMMEDIATE '
      CREATE TABLE drop_objects_log (
        log_id NUMBER GENERATED ALWAYS AS IDENTITY,
        schema_name VARCHAR2(30),
        object_type VARCHAR2(30),
        object_name VARCHAR2(128),
        drop_status VARCHAR2(20),
        error_message VARCHAR2(4000),
        drop_time TIMESTAMP
      )';
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE = -955 THEN -- Table already exists
        NULL;
      ELSE
        RAISE;
      END IF;
  END;
  -- Log procedure
  PROCEDURE log_action(p_object_type VARCHAR2, p_object_name VARCHAR2, p_status VARCHAR2, p_error VARCHAR2 DEFAULT NULL) IS
  BEGIN
    INSERT INTO drop_objects_log (schema_name, object_type, object_name, drop_status, error_message, drop_time)
    VALUES (v_schema, p_object_type, p_object_name, p_status, p_error, SYSTIMESTAMP);
    COMMIT;
  END;
  -- Check if object exists
  FUNCTION object_exists(p_object_type VARCHAR2, p_object_name VARCHAR2) RETURN BOOLEAN IS
    v_exists NUMBER;
  BEGIN
    SELECT COUNT(*)
    INTO v_exists
    FROM all_objects
    WHERE owner = v_schema
      AND object_name = p_object_name
      AND object_type = p_object_type;
    RETURN v_exists > 0;
  END;
BEGIN
  v_start_time := SYSTIMESTAMP;
  DBMS_OUTPUT.PUT_LINE('Starting schema cleanup for ' || v_schema || ' at ' || v_start_time);
  -- Create log table
  create_log_table;
  -- Array of object types to drop in specific order to handle dependencies
  FOR obj_type IN (
    SELECT object_type
    FROM (
      SELECT 'TABLE' AS object_type FROM dual UNION ALL
      SELECT 'VIEW' FROM dual UNION ALL
      SELECT 'MATERIALIZED VIEW' FROM dual UNION ALL
      SELECT 'INDEX' FROM dual UNION ALL
      SELECT 'TRIGGER' FROM dual UNION ALL
      SELECT 'SYNONYM' FROM dual UNION ALL
      SELECT 'SEQUENCE' FROM dual UNION ALL
      SELECT 'PACKAGE' FROM dual UNION ALL
      SELECT 'PROCEDURE' FROM dual UNION ALL
      SELECT 'FUNCTION' FROM dual UNION ALL
      SELECT 'TYPE' FROM dual UNION ALL
      SELECT 'JOB' FROM dual UNION ALL
      SELECT 'QUEUE' FROM dual
    )
  ) LOOP
    -- Query objects of the current type
    FOR obj IN (
      SELECT object_name
      FROM all_objects
      WHERE owner = v_schema
        AND object_type = obj_type.object_type
        AND object_name NOT LIKE 'SYS_%' -- Exclude system-generated objects
      ORDER BY object_name
    ) LOOP
      BEGIN
        -- Verify object exists
        IF object_exists(obj_type.object_type, obj.object_name) THEN
          v_sql := CASE obj_type.object_type
            WHEN 'TABLE' THEN 'DROP TABLE ' || v_schema || '.' || obj.object_name || ' CASCADE CONSTRAINTS PURGE'
            WHEN 'VIEW' THEN 'DROP VIEW ' || v_schema || '.' || obj.object_name
            WHEN 'MATERIALIZED VIEW' THEN 'DROP MATERIALIZED VIEW ' || v_schema || '.' || obj.object_name
            WHEN 'INDEX' THEN 'DROP INDEX ' || v_schema || '.' || obj.object_name
            WHEN 'TRIGGER' THEN 'DROP TRIGGER ' || v_schema || '.' || obj.object_name
            WHEN 'SYNONYM' THEN 'DROP SYNONYM ' || v_schema || '.' || obj.object_name
            WHEN 'SEQUENCE' THEN 'DROP SEQUENCE ' || v_schema || '.' || obj.object_name
            WHEN 'PACKAGE' THEN 'DROP PACKAGE ' || v_schema || '.' || obj.object_name
            WHEN 'PROCEDURE' THEN 'DROP PROCEDURE ' || v_schema || '.' || obj.object_name
            WHEN 'FUNCTION' THEN 'DROP FUNCTION ' || v_schema || '.' || obj.object_name
            WHEN 'TYPE' THEN 'DROP TYPE ' || v_schema || '.' || obj.object_name || ' FORCE'
            WHEN 'JOB' THEN 'BEGIN DBMS_SCHEDULER.DROP_JOB(''' || v_schema || '.' || obj.object_name || '''); END;'
            WHEN 'QUEUE' THEN 'BEGIN DBMS_AQADM.DROP_QUEUE(''' || obj.object_name || '''); END;'
            ELSE 'DROP ' || obj_type.object_type || ' ' || v_schema || '.' || obj.object_name
          END;
          -- Execute drop statement with parallel hint for large tables
          IF obj_type.object_type = 'TABLE' THEN
            EXECUTE IMMEDIATE '/*+ PARALLEL(4) */ ' || v_sql;
          ELSE
            EXECUTE IMMEDIATE v_sql;
          END IF;
          log_action(obj_type.object_type, obj.object_name, 'SUCCESS');
          DBMS_OUTPUT.PUT_LINE('Dropped ' || obj_type.object_type || ': ' || obj.object_name);
        END IF;
      EXCEPTION
        WHEN OTHERS THEN
          log_action(obj_type.object_type, obj.object_name, 'FAILED', SQLERRM);
          DBMS_OUTPUT.PUT_LINE('Error dropping ' || obj_type.object_type || ' ' || obj.object_name || ': ' || SQLERRM);
          -- Continue with next object instead of failing the entire script
          CONTINUE;
      END;
    END LOOP;
  END LOOP;
  -- Verify cleanup
  SELECT COUNT(*)
  INTO v_count
  FROM all_objects
  WHERE owner = v_schema
    AND object_type NOT IN ('LOB', 'INDEX PARTITION', 'TABLE PARTITION'); -- Exclude system-managed objects
  IF v_count = 0 THEN
    DBMS_OUTPUT.PUT_LINE('Schema ' || v_schema || ' is clean. No objects remain.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Warning: ' || v_count || ' objects remain in schema ' || v_schema);
  END IF;
  DBMS_OUTPUT.PUT_LINE('Cleanup completed in ' || 
    ROUND(EXTRACT(SECOND FROM (SYSTIMESTAMP - v_start_time)), 2) || ' seconds');
  -- Output log summary
  FOR log_rec IN (SELECT object_type, object_name, drop_status, error_message 
                  FROM drop_objects_log 
                  WHERE schema_name = v_schema 
                  ORDER BY drop_time) LOOP
    DBMS_OUTPUT.PUT_LINE('Log: ' || log_rec.object_type || ' ' || log_rec.object_name || 
                         ' - Status: ' || log_rec.drop_status || 
                         CASE WHEN log_rec.error_message IS NOT NULL THEN ' - Error: ' || log_rec.error_message END);
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Fatal error in script: ' || SQLERRM);
    RAISE;
END;
/