OracleObjects & schema

Invalid objects

Every invalid object by owner and type, typically left behind by a deployment or patch. The recompile script fixes most of them.

Tested on 12c, 19c · verified Sep 2026

b-ora-invalid.sql

1CLEAR COLUMNS2SET LINESIZE 150 PAGESIZE 200 TRIMOUT ON TAB OFF3COLUMN owner FORMAT A204COLUMN object_type FORMAT A205COLUMN object_name FORMAT A306SELECT owner,7       object_type,8       object_name,9       status10FROM   dba_objects11WHERE  status = 'INVALID'12ORDER BY owner, object_type, object_name;

Save it as b-ora-invalid.sql and run it with SQL> @b-ora-invalid.

Open in denrepo

Helps with

ORA-04068: existing state of packages%s%s%s has been discarded

Part of these runbooks

More Oracle scripts: Objects & schema

Generate DDL for a table and an indexUses DBMS_METADATA to write the CREATE statements for a table and one of its indexes into ddl_list.sql in your current directory. Prompts for the…
Recompile invalid objectsRecompile one schema, or everything in the database with Oracle's utlrp script. Run the invalid objects check again afterwards.
Unusable indexes and index partitionsIndexes marked UNUSABLE, typically after a partition operation or a direct-path load. Queries that need them will either fail or fall back to full…
Foreign keys that are probably not indexedForeign keys whose columns aren't the leading columns of an index. Unindexed foreign keys cause table-level locks when the parent row is updated or…
Disabled constraints and triggersConstraints and triggers someone switched off, often for a data load, and never switched back on.
Objects changed in the last 24 hoursApplication objects with DDL in the last day. Grants also update LAST_DDL_TIME, so not every row is a code change.