Find Invalid Objects in Oracle

Lists INVALID objects in user schemas. Essential after upgrades and patching.

#invalid-objects#compile#post-upgrade

oracle · sql

SELECT owner, object_type, object_name, status, last_ddl_time
FROM   dba_objects
WHERE  status = 'INVALID'
  AND  owner NOT IN ('SYS','SYSTEM')
ORDER  BY owner, object_type, object_name;

Operational Notes & Remediation Guidance

Recompile invalid objects with `ALTER [OBJECT_TYPE] [OBJECT_NAME] COMPILE;` or run `utlrp.sql`.

Production Safety & Execution Standards

This script is designed for read-only diagnostics; it does not alter schema or modify data directly.
Prior to executing on production systems under high load, verify the target database version (Min. Version: 10g).
Exercise caution with mutating actions (e.g., terminating sessions or dropping indexes) referenced in the notes section.

Engineering Triad: Related Runbooks & Incident Response

Which production incidents and emergency drills execute this script as a diagnosis step?

Runbook Library (63)

Proactive Maintenance & Health Check: This script is not tied to an active incident alert; it is intended for scheduled maintenance, capacity planning, and routine DBA audits.

Other ORACLE Diagnostic Scripts

View All (100)

#61Performance

Oracle Active Sessions and Wait Events

Lists active user sessions with the running SQL_ID and wait event. Shows the live picture during performance issues.

View Script

#62Locking

Find Blocking Sessions in Oracle

Shows sessions that make others wait, with wait times. Identifies the root session to terminate in lock incidents.

View Script

#63Storage

Oracle Tablespace Usage Including Autoextend

Calculates tablespace usage against the real maximum, honoring autoextend limits. Catches space errors like ORA-01653 before they happen.

View Script

#64Performance

Top 20 Most Expensive Oracle SQL with v$sqlstats

Shows costliest SQL by elapsed time with executions, CPU and I/O. Tells you which SQL_ID to investigate for tuning.

View Script