Identify and Fix Invalid Objects in Oracle Database

calendar Feb 24, 2024 / · 9 min read · oracle administration objects dba_objects dba_dependencies dba_errors dba_hist_filestatxs dba_hist_sqlstat dba_hist_sqlbind dba_hist_tablespace_stat dba_hist_service_stat dba_hist_service_wait_class  ·
Share on: linkedin copy

How to Identify and Fix Invalid Objects in an Oracle Database

Purpose

A list of invalid objects tells you more than it first seems to. Ten invalid objects in one application schema right after a release can point to one changed table. Hundreds of invalid objects across many schemas more often follow a patch or an upgrade. The size and the shape of the list is the first clue to the cause.

This check reads DBA_OBJECTS and keeps only the rows where STATUS is INVALID. It shows the owner, the name and the type of each object. A second query counts the invalid objects by owner and type, so you see the pattern before you read the detail. Two more queries trace one object back to the objects it depends on and to the compile errors it holds.

Finding the objects and deciding what to do with them is the subject here. The commands that recompile objects are in Recompile Invalid Objects in Oracle Database. How to read a compile error is in SHOW ERRORS and dba_errors Explained.

Code

 1-- 1. List every invalid object
 2set lines 200 pages 999
 3col "obj" format a40
 4select owner || '.' || object_name "obj",
 5object_type
 6from dba_objects
 7where status = 'INVALID'
 8/
 9
10-- 2. Count invalid objects by owner and type
11select owner, object_type, count(*) invalid_count
12from dba_objects
13where status = 'INVALID'
14group by owner, object_type
15order by owner, object_type
16/
17
18-- 3. See when each invalid object was last changed by DDL
19select owner || '.' || object_name "obj", object_type, last_ddl_time
20from dba_objects
21where status = 'INVALID'
22order by last_ddl_time desc
23/
24
25-- 4. For one invalid object, list what it depends on
26select referenced_owner, referenced_name, referenced_type
27from dba_dependencies
28where owner = 'WEBUSER'
29and name = 'MAILER'
30and type = 'PACKAGE'
31/
32
33-- 5. For one invalid object, read the stored compile errors
34select text
35from dba_errors
36where owner = 'WEBUSER'
37and name = 'MAILER'
38and type = 'PACKAGE'
39/

Sample Oracle Output

 1OBJ                                      OBJECT_TYPE
 2---------------------------------------- --------------------
 3WEBUSER.CHECK                            PACKAGE BODY
 4WEBUSER.MAILER                           PACKAGE
 5WEBUSER.APP_FUNCS                        PACKAGE BODY
 6PUBLIC.DBA_HIST_FILESTATXS               SYNONYM
 7PUBLIC.DBA_HIST_SQLSTAT                  SYNONYM
 8PUBLIC.DBA_HIST_SQLBIND                  SYNONYM
 9PUBLIC.DBA_HIST_TABLESPACE_STAT          SYNONYM
10PUBLIC.DBA_HIST_SERVICE_STAT             SYNONYM
11PUBLIC.DBA_HIST_SERVICE_WAIT_CLASS       SYNONYM

The names WEBUSER, MAILER and PACKAGE in queries 4 and 5 are examples. Replace them with a row from your own query 1 output.

Code Breakdown

Query 1: the full list

  • set lines 200 pages 999 sets two SQL*Plus display options. SET LINESIZE sets the total number of characters SQL*Plus displays on one line before it begins a new line. SET PAGESIZE sets the number of lines on each page of output. A page size of 999 means the column headings repeat less often on a long result.
  • col "obj" format a40 gives the combined name a width of 40 characters.
  • owner || '.' || object_name "obj" joins the owner, a dot and the object name into one column. You read WEBUSER.MAILER at a glance.
  • from dba_objects is the right source for a database-wide check. DBA_OBJECTS describes all objects in the database.
  • where status = 'INVALID' keeps the rows you care about. The STATUS column holds one of three values: VALID, INVALID or N/A.

Query 2: the count by owner and type

Query 2 groups the same rows by owner and object_type. Read it before you read query 1. A count such as 3 package bodies in one schema is a small job. A count such as 400 views and 900 synonyms across many schemas is a different job, and you handle it in bulk.

Query 3: the timing clue

LAST_DDL_TIME is the timestamp for the last modification of the object and dependent objects resulting from a DDL statement. If many invalid objects share a recent time, one DDL change or one patch run probably caused them. Compare that time with your change log.

Queries 4 and 5: the cause

  • dba_dependencies describes dependencies in the database between procedures, packages, functions, package bodies, and triggers, including dependencies on views created without any database links. The referenced_* columns name the objects that your invalid object points to. Check each one for a recent change, a drop, or an invalid status of its own.
  • dba_errors describes the current errors on all stored objects in the database. The text column holds the text of the error. Read it for each invalid object that matters to you.

Key Points

  • Invalid is not always a fault. An object becomes invalid when its compile-time references cannot be resolved, or when a referenced object changes in a way that might affect it. The database tracks these links on its own.
  • Invalidation spreads. Dependent objects are cascade invalidated. If any object becomes invalid for any reason, all of that object's dependent objects are immediately invalidated. One bad table can produce a long list.
  • Privilege changes count. If you revoke any object privileges on a schema object, dependent objects are cascade invalidated.
  • The database fixes many of them for you. An invalid dependent object must be recompiled against the new definition of a referenced object before it can be used. Recompilation occurs automatically when the invalid dependent object is referenced.
  • Automatic repair has a cost. An invalid object must be revalidated before an application can use it. Revalidation adds latency to application execution. If the number of invalid objects is large, the added latency on the first execution can be significant.
  • Other sessions can fail. Invalidation of a procedure, function or package can cause exceptions in other sessions that run it at the same time. Those exceptions must be remedied by restarting application sessions following a patch.

Insights and Best Practices

Decide by count and by cause

Use the output of queries 2 to 5 to place the list in one of three groups.

  • A few objects, a known recent change. Recompile them by hand with the COMPILE clause of the matching ALTER statement, then run query 1 again.
  • Many objects after a patch or upgrade. Use the bulk method. If you know that there are a large number of invalid objects, use the UTL_RECOMP PL/SQL package to perform a mass recompilation. The steps are in the recompile post linked above.
  • Objects that return to invalid after a recompile. Do not recompile them again. Read their rows in dba_errors and fix the code or the missing referenced object first.

Know what a normal baseline looks like

In a typical running application, you would not expect to see views or stored procedures become invalid, because applications typically do not change table structures or change view or stored procedure definitions during normal execution. Changes to tables, views, or PL/SQL units typically occur when an application is patched or upgraded using a patch script or ad-hoc DDL statements. Dependent objects might be left invalid after a patch has been applied to change a set of referenced objects.

So a non-zero count on a quiet system with no change window is worth a closer look. A non-zero count right after a change window is expected, and you clear it as part of the change.

Save the count before and after each change

Run query 2 before a patch, and save the output. Run it again after the patch and after the repair. Compare the three results. Any object that was valid before and invalid after is a regression you can name. Any object that was already invalid before the patch is not yours to fix in this change.

Repair before users arrive

After an application upgrade or patch, revalidate invalid objects so that users do not pay the on-demand latency. Do this inside the change window. Run query 1 at the end and confirm it returns no rows, or only the rows you expected.

Add the check to routine monitoring

Oracle Enterprise Manager Cloud Control displays an alert on the Database Home page when schema objects become invalid. If you do not use it, schedule query 2 with your own job tool and send the result to the team when the count rises.

Be careful with the sample output

The sample output above lists some public synonyms. Do not assume that a synonym is safe to ignore. Run query 5 for it and read the result. Treat each type in the list on its own merits.

When to Run This

  • Straight after a patch, an upgrade or a release script.
  • After any change to a table, view or package that others depend on.
  • After you revoke privileges from a schema or a role.
  • When an application raises an error about a package or procedure that worked before.
  • When a first call to an application feels slow after a change window.
  • As part of a daily or weekly health check.
  • Before you hand a database to another team or promote a copy to a new environment.

Troubleshooting Common Issues

The list is empty but the application still fails. Query 1 only shows objects that are marked invalid now. An object can be valid and still hold a logic fault. Read the application error first and use query 5 on the object it names.

The list is very long and keeps growing. Dependents are cascade invalidated, so one root object can invalidate many others. Use query 4 to walk from a dependent object back to what it references. Find the object at the end of the chain that has its own error. Fix that one first and the rest may clear when they are recompiled.

An object is invalid but dba_errors has no rows for it. Check its row in query 4 for a referenced object that changed. Recompile it by hand and then run query 5 again.

An object comes back invalid after each recompile. Read the text column from query 5, fix the cause, then recompile.

A referenced object does not exist. If a nonexistent object is created later, all dependent objects must be invalidated so that dependent objects can be recompiled and verified. Expect that kind of invalidation when you create an object whose name other code already uses.

Recompiling blocks or runs slowly. Do not start a mass recompile while other DDL runs. Pick a quiet period and run it once.

References

Posts in this series