PL/SQL Script to fix Invalid Objects on AWS RDS

 SET SERVEROUTPUT ON
DECLARE
  v_invalid_count NUMBER;
  v_start_time    TIMESTAMP;
  v_end_time      TIMESTAMP;
BEGIN
  -- Record start time
  v_start_time := SYSTIMESTAMP;
  DBMS_OUTPUT.PUT_LINE('Recompilation check started at: ' || TO_CHAR(v_start_time, 'YYYY-MM-DD HH24:MI:SS'));

  -- Check number of invalid objects
  SELECT COUNT(*)
  INTO v_invalid_count
  FROM dba_objects
  WHERE status = 'INVALID';

  DBMS_OUTPUT.PUT_LINE('Number of invalid objects: ' || v_invalid_count);

  -- Only run if there are invalids
  IF v_invalid_count > 0 THEN
    DBMS_OUTPUT.PUT_LINE('Starting recompilation...');
    
    -- Run parallel recompilation (adjust degree if needed)
    UTL_RECOMP.RECOMP_PARALLEL(4);

    DBMS_OUTPUT.PUT_LINE('Recompilation complete.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('No invalid objects found. No recompilation needed.');
  END IF;

  -- Record end time
  v_end_time := SYSTIMESTAMP;
  DBMS_OUTPUT.PUT_LINE('Recompilation check finished at: ' || TO_CHAR(v_end_time, 'YYYY-MM-DD HH24:MI:SS'));
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);
END;

/

=======

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  CURSOR cur_invalid_schemas IS
    SELECT DISTINCT owner
    FROM dba_objects
    WHERE status = 'INVALID'
      AND owner NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'SYSMAN') -- Skip Oracle system users
    ORDER BY owner;
  v_schema_name   dba_objects.owner%TYPE;
  v_invalid_count NUMBER;
  v_start_time    TIMESTAMP;
  v_end_time      TIMESTAMP;
BEGIN
  -- Record overall start time
  v_start_time := SYSTIMESTAMP;
  DBMS_OUTPUT.PUT_LINE('Multi-schema recompilation started at: ' || TO_CHAR(v_start_time, 'YYYY-MM-DD HH24:MI:SS'));
  -- Open cursor
  OPEN cur_invalid_schemas;
  LOOP
    FETCH cur_invalid_schemas INTO v_schema_name;
    EXIT WHEN cur_invalid_schemas%NOTFOUND;
    -- Count invalid objects for this schema
    SELECT COUNT(*)
    INTO v_invalid_count
    FROM dba_objects
    WHERE status = 'INVALID'
      AND owner = v_schema_name;
    DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');
    DBMS_OUTPUT.PUT_LINE('Schema: ' || v_schema_name || ' | Invalid Objects: ' || v_invalid_count);
    -- If schema has invalids, recompile
    IF v_invalid_count > 0 THEN
      BEGIN
        DBMS_OUTPUT.PUT_LINE('Starting recompilation for schema: ' || v_schema_name);
        -- Recompile serially for that schema
        sys.UTL_RECOMP.RECOMP_SERIAL(v_schema_name);
        DBMS_OUTPUT.PUT_LINE('Recompilation completed for schema: ' || v_schema_name);
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('Error during recompilation for schema ' || v_schema_name || ': ' || SQLERRM);
      END;
    ELSE
      DBMS_OUTPUT.PUT_LINE('No invalid objects for schema: ' || v_schema_name);
    END IF;
  END LOOP;
  CLOSE cur_invalid_schemas;
  -- Record overall end time
  v_end_time := SYSTIMESTAMP;
  DBMS_OUTPUT.PUT_LINE('Multi-schema recompilation finished at: ' || TO_CHAR(v_end_time, 'YYYY-MM-DD HH24:MI:SS'));
  DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Unexpected error occurred: ' || SQLERRM);
END;
/
-- Optional: After all recompilations, show remaining invalid objects
PROMPT Checking for any remaining invalid objects...
SELECT owner, object_name, object_type, status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;

====

SET SERVEROUTPUT ON SIZE 1000000
SET LONG 100000
SET PAGESIZE 50000
DECLARE
  CURSOR cur_invalid_schemas IS
    SELECT DISTINCT owner
    FROM dba_objects
    WHERE status = 'INVALID'
      AND owner NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'SYSMAN') -- Exclude system schemas
    ORDER BY owner;
  v_schema_name dba_objects.owner%TYPE;
  v_invalid_count NUMBER;
  v_start_time TIMESTAMP;
  v_end_time TIMESTAMP;
  CURSOR cur_synonyms IS
    SELECT owner, synonym_name, table_owner, table_name
    FROM dba_synonyms
    WHERE synonym_name IN (
      SELECT object_name
      FROM dba_objects
      WHERE status = 'INVALID'
        AND object_type = 'SYNONYM'
    );
  v_count NUMBER := 0;
BEGIN
  -------------------------------------------------------
  -- Step 1: Loop through schemas and recompile
  -------------------------------------------------------
  v_start_time := SYSTIMESTAMP;
  DBMS_OUTPUT.PUT_LINE('=== Multi-Schema Recompilation Started at: ' || TO_CHAR(v_start_time, 'YYYY-MM-DD HH24:MI:SS'));
  OPEN cur_invalid_schemas;
  LOOP
    FETCH cur_invalid_schemas INTO v_schema_name;
    EXIT WHEN cur_invalid_schemas%NOTFOUND;
    SELECT COUNT(*)
    INTO v_invalid_count
    FROM dba_objects
    WHERE status = 'INVALID'
      AND owner = v_schema_name;
    IF v_invalid_count > 0 THEN
      DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');
      DBMS_OUTPUT.PUT_LINE('Schema: ' || v_schema_name || ' | Invalid Objects: ' || v_invalid_count);
      BEGIN
        DBMS_OUTPUT.PUT_LINE('Recompiling schema: ' || v_schema_name);
        UTL_RECOMP.RECOMP_SERIAL(v_schema_name);
        DBMS_OUTPUT.PUT_LINE('Recompilation completed for schema: ' || v_schema_name);
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('Error recompiling schema ' || v_schema_name || ': ' || SQLERRM);
      END;
    ELSE
      DBMS_OUTPUT.PUT_LINE('No invalid objects found in schema: ' || v_schema_name);
    END IF;
  END LOOP;
  CLOSE cur_invalid_schemas;
  v_end_time := SYSTIMESTAMP;
  DBMS_OUTPUT.PUT_LINE('=== Multi-Schema Recompilation Finished at: ' || TO_CHAR(v_end_time, 'YYYY-MM-DD HH24:MI:SS'));
  -------------------------------------------------------
  -- Step 2: Report broken SYNONYMS across all schemas
  -------------------------------------------------------
  DBMS_OUTPUT.PUT_LINE(CHR(10) || '=== Checking broken SYNONYMS across all schemas ===');
  FOR syn IN cur_synonyms LOOP
    BEGIN
      SELECT COUNT(*)
      INTO v_count
      FROM dba_objects
      WHERE owner = syn.table_owner
        AND object_name = syn.table_name;
      IF v_count = 0 THEN
        DBMS_OUTPUT.PUT_LINE('BROKEN SYNONYM: ' || syn.owner || '.' || syn.synonym_name ||
                             ' --> Missing ' || syn.table_owner || '.' || syn.table_name);
      ELSE
        DBMS_OUTPUT.PUT_LINE('Synonym ' || syn.owner || '.' || syn.synonym_name ||
                             ' --> Target exists (' || syn.table_owner || '.' || syn.table_name || '), but still invalid.');
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error checking synonym ' || syn.owner || '.' || syn.synonym_name || ': ' || SQLERRM);
    END;
  END LOOP;
END;
/
-------------------------------------------------------
-- Step 3: Report invalid VIEWS across all schemas
-------------------------------------------------------
PROMPT
PROMPT === Checking broken VIEWS across all schemas ===
SELECT 'View: ' || owner || '.' || object_name || CHR(10) ||
       DBMS_METADATA.GET_DDL('VIEW', object_name, owner) || CHR(10) ||
       '-------------------------------------------'
FROM dba_objects
WHERE status = 'INVALID'
  AND object_type = 'VIEW'
ORDER BY owner, object_name;
-------------------------------------------------------
-- Step 4: Final list of any remaining invalid objects
-------------------------------------------------------
PROMPT
PROMPT === Final invalid objects report ===
SELECT owner, object_name, object_type, status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;