🧩 How to Check Invalid Objects in Oracle Database

 🧩 Introduction

In Oracle databases, objects such as views, procedures, packages, and triggers can become invalid after schema changes, upgrades, or when dependent objects are altered. Monitoring and fixing invalid objects is essential to keep applications running smoothly.

---

Step 1: Query for Invalid Objects

Run the following query to list all invalid objects:

SELECT object_name, object_type, status

FROM dba_objects

WHERE status = 'INVALID'

ORDER BY object_type;

➡️ This gives you a clear list of which objects need attention.

---

Step 2: Compile a Single Object

To recompile a specific invalid object:

ALTER PACKAGE package_name COMPILE;

Or for a procedure:

ALTER PROCEDURE procedure_name COMPILE;

➡️ Replace with the actual object name.

---

Step 3: Recompile All Invalid Objects

Use Oracle’s built-in script to compile all invalid objects:

@?/rdbms/admin/utlrp.sql

➡️ This script recompiles all invalid PL/SQL modules in the database.

---

Step 4: Verify the Status Again

After recompilation, re-run the query from Step 1 to confirm that all objects are now VALID.

---

Step 5: Investigate Persistent Invalid Objects

If some objects remain invalid:

Check missing privileges or dependent objects.

Ensure referenced objects exist and are valid.

Review the compilation errors with:

SHOW ERRORS PACKAGE package_name;

---

Conclusion

Managing invalid objects ensures database consistency and prevents application errors. A best practice is to run the utlrp.sql script after migrations, patches, or schema updates to keep your Oracle database objects healthy.

---