Dealing with invalid database objects

After schema changes, upgrades, or failed deployments, you'll often end up with invalid packages, views, or triggers. Finding and fixing them is a routine maintenance task.

Recipe #1 - List all invalid objects

SELECT owner, object_type, object_name, status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;

If you only want to check your own schema:

SELECT object_type, object_name, status
FROM user_objects
WHERE status = 'INVALID'
ORDER BY object_type, object_name;

Recipe #2 - Recompile a single object

Recompile specific object types:

-- Package specification and body
ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;

-- View
ALTER VIEW my_view COMPILE;

-- Trigger
ALTER TRIGGER my_trigger COMPILE;

-- Function or Procedure
ALTER FUNCTION my_function COMPILE;
ALTER PROCEDURE my_procedure COMPILE;

Recipe #3 - Recompile all invalid objects automatically

Use Oracle's built-in UTL_RECOMP to recompile everything. It even handles dependencies:

-- Recompile all invalid objects (serial)
EXEC UTL_RECOMP.recomp_serial();

-- Recompile in parallel (faster for many objects)
EXEC UTL_RECOMP.recomp_parallel(4);

-- Recompile only a specific schema
EXEC UTL_RECOMP.recomp_serial('MY_SCHEMA');

Recipe #4 - PL/SQL block to recompile with error reporting

A more controlled approach that reports success and failure:

BEGIN
  FOR obj IN (
    SELECT object_type, object_name
    FROM user_objects
    WHERE status = 'INVALID'
    ORDER BY DECODE(object_type, 'PACKAGE', 1, 'PACKAGE BODY', 2,
                    'FUNCTION', 3, 'PROCEDURE', 4, 'VIEW', 5,
                    'TRIGGER', 6, 9)
  ) LOOP
    BEGIN
      IF obj.object_type = 'PACKAGE BODY' THEN
        EXECUTE IMMEDIATE 'ALTER PACKAGE ' || obj.object_name
          || ' COMPILE BODY';
      ELSE
        EXECUTE IMMEDIATE 'ALTER ' || obj.object_type
          || ' ' || obj.object_name || ' COMPILE';
      END IF;
      DBMS_OUTPUT.put_line('OK: ' || obj.object_type
        || ' ' || obj.object_name);
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('FAIL: ' || obj.object_type
          || ' ' || obj.object_name || ' — ' || SQLERRM);
    END;
  END LOOP;
END;

After recompilation, invalid objects usually fall into two categories: objects that were temporarily invalid and are now fixed, and objects with genuine code errors that need manual attention.