Oracle problems

Invalid Objects and Unusable Indexes: Which Ones, Why, and the Fix

3 November 2026

An invalid object is a package, view, trigger or type whose referenced objects changed under it. Oracle recompiles it on first use, and when that compile fails the user gets the error instead of the data. An unusable index is one the optimizer has to ignore, so the statement that relied on it reads the whole table and nobody is told. Both come from the same events, a deploy, a partition maintenance operation, a table move, and both sit in the dictionary for days before the first complaint. Three queries list them with the reason, and the fix is a rebuild and a recompile in the right order.

Which objects are invalid, and why

Invalid objects by owner and type, with the time of the last DDL on them, which includes grants and revokes:

SELECT owner, object_type, COUNT(*)      AS invalid,
       MIN(last_ddl_time)                AS oldest_ddl,
       MAX(last_ddl_time)                AS newest_ddl
FROM   dba_objects
WHERE  status = 'INVALID'
GROUP  BY owner, object_type
ORDER  BY invalid DESC;

Two kinds hide in that count. An object that is invalid with no error is a dependent that was invalidated when something it references changed; Oracle's manual says recompilation happens automatically when the dependent object is referenced, so it compiles on first use and the only cost is that first call. An object with an error does not compile, and every call fails until someone fixes it. DBA_ERRORS separates them:

SELECT e.owner, e.name, e.type, e.line, e.position, e.text
FROM   dba_errors e
WHERE  e.attribute = 'ERROR'
ORDER  BY e.owner, e.name, e.sequence;

The texts repeat: PLS-00201, identifier must be declared, is a table, synonym or grant that did not travel with the deploy; PLS-00306, wrong number or types of arguments, is a caller of a procedure whose signature changed; ORA-00942 inside a view or a package body is a table that was dropped or a grant that was revoked. The referenced objects that caused the invalidations, with the time they changed:

SELECT d.referenced_owner, d.referenced_name, d.referenced_type,
       MAX(r.last_ddl_time)                  AS changed,
       COUNT(*)                              AS dependents_invalid
FROM   dba_dependencies d
JOIN   dba_objects o ON  o.owner       = d.owner
                     AND o.object_name = d.name
                     AND o.object_type = d.type
JOIN   dba_objects r ON  r.owner       = d.referenced_owner
                     AND r.object_name = d.referenced_name
                     AND r.object_type = d.referenced_type
WHERE  o.status = 'INVALID'
GROUP  BY d.referenced_owner, d.referenced_name, d.referenced_type
ORDER  BY changed DESC;

One table altered at 02:10 with forty dependents under it is the usual answer, and the time names the deploy. Oracle's manual: dependent objects are invalidated in a cascade, so one changed table takes its views, their views, and the packages that call them.

Which indexes are unusable

Whole indexes, index partitions and subpartitions, in one list. The partitioned ones are the ones that get missed, because the index itself still shows as valid:

SELECT owner, index_name, NULL AS part, 'INDEX' AS level_
FROM   dba_indexes
WHERE  status = 'UNUSABLE'
UNION ALL
SELECT index_owner, index_name, partition_name, 'PARTITION'
FROM   dba_ind_partitions
WHERE  status = 'UNUSABLE'
UNION ALL
SELECT index_owner, index_name, subpartition_name, 'SUBPARTITION'
FROM   dba_ind_subpartitions
WHERE  status = 'UNUSABLE'
ORDER  BY 1, 2, 3;

An index becomes unusable when the rows it points to move: ALTER TABLE ... MOVE without UPDATE INDEXES, a partition split, merge, exchange, truncate or drop without UPDATE INDEXES for the global and local indexes it touches, a direct-path load that skipped index maintenance, a rebuild that failed halfway. The default of SKIP_UNUSABLE_INDEXES is TRUE, and Oracle's reference describes what that does: error reporting for unusable indexes is disabled and all operations on the table are allowed, which means the optimizer silently plans around the index and the statement that used it runs as a full scan. Two exceptions still raise an error: a unique index, because the setting does not disable error reporting for unusable indexes that are unique, so inserts fail with ORA-01502; and a statement whose hint forces the unusable index, which gets ORA-01502 as well.

The fix, in order

Rebuild the unique indexes first. They block inserts and updates now; the others only slow reads. ALTER INDEX ... REBUILD for a whole index, REBUILD PARTITION and REBUILD SUBPARTITION for the pieces, or ALTER TABLE ... MODIFY PARTITION ... REBUILD UNUSABLE LOCAL INDEXES for every local index of a partition in one statement. ONLINE keeps the table open on Enterprise Edition; on Standard Edition 2 the rebuild takes a lock, so it goes in a quiet window.
Recompile everything that can be recompiled. UTL_RECOMP.RECOMP_PARALLEL recompiles the invalid objects of the database or a schema using the dependency order, which is what utlrp.sql runs after a patch; DBMS_UTILITY.COMPILE_SCHEMA does one schema. Run it after the deploy, not at the peak: a thousand first-use compiles at nine o'clock is a library cache storm.
Fix what still has an error. After the recompile, DBA_ERRORS holds only the real breakage. The missing grant or synonym goes in the deploy script, the caller of the changed signature is changed too, the view on the dropped column is rewritten. The order for next time: referenced objects first, grants in the same script, a recompile step at the end, and the invalid count checked before the change window closes.
Keep the indexes usable in the first place. UPDATE INDEXES on every partition maintenance operation and on ALTER TABLE ... MOVE from 12.2, so the operation maintains the indexes instead of marking them. The statement takes longer; the alternative is a day of full scans and a rebuild anyway.
Do not hunt with SKIP_UNUSABLE_INDEXES = FALSE in production. It turns the silent slow plan into an error for every statement that would use the index. Right in a test database, wrong on a Monday morning; the dictionary query above finds them without breaking anything.

See it coming

Radar Engine, the detection layer of Scops, counts the invalid objects and the unusable indexes of every database once a day and reports the jump, not the count, so a schema that has carried twelve invalid objects for years stays quiet and the forty new ones after a deploy do not. The objects that do not compile are listed separately, because they are the ones with a user behind them:

Detector	Clock	When it fires
Invalid objects	Daily. A jump in the week: 10 more, or 5 more and twice as many. Also objects that do not compile.	Watch. More that do not compile this week: Warning.
Unusable indexes	Daily. Also index partitions.	Watch. Rising, and 10 or more: Warning.

When an unusable index sends a statement to a full scan, the plan-change detector names the statement and the day its plan moved, so the two findings arrive together: the index on the one side, the statement that lost it on the other. Scops AI reads both and proposes the rebuild and the recompile, and changes nothing without a click.

Questions

How do I find invalid objects in Oracle?
DBA_OBJECTS with STATUS = 'INVALID', grouped by owner and type. DBA_ERRORS holds the compile errors; the invalid objects without a row there compile on first use.

Do invalid objects fix themselves?
Oracle recompiles an invalid dependent object automatically when it is next referenced. If the compile fails, the user gets the error instead; those are the objects with rows in DBA_ERRORS, and they need a fix, not a recompile.

How do I find unusable indexes, including partitions?
DBA_INDEXES, DBA_IND_PARTITIONS and DBA_IND_SUBPARTITIONS with STATUS = 'UNUSABLE'. The partition rows are the ones that hide: the index itself still reads as valid while a partition of it is unusable.

Why is the database slow after partition maintenance?
The operation marked local or global indexes unusable, and with SKIP_UNUSABLE_INDEXES at its default of TRUE the optimizer plans around them without an error, so the statements that used them now scan. Rebuild them, and add UPDATE INDEXES to the operation next time.

Sources

Oracle: DBA_OBJECTS, DBA_ERRORS, DBA_INDEXES, DBA_IND_PARTITIONS, SKIP_UNUSABLE_INDEXES, UTL_RECOMP
Oracle: Managing schema objects (dependencies and recompilation)
Scops docs: Detector reference