🧩 How to Check Invalid Objects in Oracle Database

🧩 Introduction

Invalid objects in Oracle, such as procedures, views, or packages, can cause application errors. DBAs should regularly check and recompile invalid objects to ensure database stability.

---

Step 1: Find Invalid Objects

SELECT object_name, object_type, status

FROM dba_objects

WHERE status='INVALID';

➡️ Lists all invalid objects in the database.

---

Step 2: Recompile a Specific Object

ALTER PACKAGE package_name COMPILE;

ALTER PROCEDURE procedure_name COMPILE;

➡️ Replace package_name or procedure_name with the object you want to recompile.

---

Step 3: Recompile All Invalid Objects

@?/rdbms/admin/utlrp.sql

➡️ This script recompiles all invalid objects in the database.

---

Conclusion

Regularly checking and recompiling invalid objects helps maintain a healthy Oracle environment and reduces application errors.

---