If It Ain't Fixed...

Occasionally certain objects in a database can fail to function, such as packages, procedures, triggers, functions, indexes, synonyms and views. Modifying any of the underlying dependent objects those items rely upon can change the status of such objects from 'VALID' to 'INVALID' or 'UNUSABLE'. Short of attempting to access such objects and failing miserably how does one discover and correct such problems? The solution is fairly simple: ask the database.

Oracle provides, in the *_OBJECTS views, a column named STATUS which lists the status (obviously) of the object in question. When everything is right and proper that status should be 'VALID'; of course no database is problem-free or immune to code changes so there are times when various objects may no longer be usable. Finding these objects is half of the battle; let's look at a query to do that:

SQL> select object_name, object_type, status
  2  from user_objects
  3  where status <> 'VALID'
  4  order by created;


OBJECT_NAME                         OBJECT_TYPE         STATUS
----------------------------------- ------------------- -------
OWA_SYLK                            PACKAGE             INVALID
SCHEMA_ACCESS                       PROCEDURE           INVALID
CHECK_SAL                           FUNCTION            INVALID
RAISE_SAL                           PROCEDURE           INVALID
STRINGC                             FUNCTION            INVALID
JOB_PKG                             PACKAGE             INVALID
DATES_PKG                           PACKAGE             INVALID
EMP_VW                              VIEW                INVALID
VIEW_EMP_DEPT                       VIEW                INVALID
GET_EMPNAME                         FUNCTION            INVALID
GET_SAL                             PROCEDURE           INVALID
PROJECT                             SYNONYM             INVALID

12 rows selected.

SQL>

Notice the objects are ordered by their creation date; this allows the query to be used to write a dynamic script to recompile the objects and avoid dependency invalidations in the process:

SQL> select 'alter '||object_type||' '||object_name||' compile;'
  2  from user_objects
  3  where status <> 'VALID'
  4  and object_type in  ('PACKAGE','PROCEDURE','FUNCTION','TYPE','VIEW', 'TRIGGER','SYNONYM')
  5  union
  6  select 'alter '||substr(object_type, 1, instr(object_type, ' BODY') -1)||' '||object_name||' compile body;'
  7  from user_objects
  8  where status <> 'VALID'
  9  and instr(object_type, ' BODY') > 0
 10  /

'ALTER'||OBJECT_TYPE||''||OBJECT_NAME||'COMPILE;'
--------------------------------------------------------------------------------
alter FUNCTION CHECK_SAL compile;
alter FUNCTION GET_EMPNAME compile;
alter FUNCTION STRINGC compile;
alter PACKAGE DATES_PKG compile;
alter PACKAGE JOB_PKG compile;
alter PACKAGE OWA_SYLK compile;
alter PROCEDURE GET_SAL compile;
alter PROCEDURE RAISE_SAL compile;
alter PROCEDURE SCHEMA_ACCESS compile;
alter SYNONYM PrOJECT compile;
alter VIEW EMP_VW compile;
alter VIEW VIEW_EMP_DEPT compile;

12 rows selected.

SQL>

But, wait, there's a neat little script that Oracle has provided to do the same job: utlrp.sql, located in the $ORACLE_HOME/rdbms/admin directory. It calls the UTL_RECOMP package and recompiles all invalid objects in the database (or tries to). It also reports how many of the recompiled objects generated errors and, if this number is larger than you might expect (yes, you may have invalid objects which cannot be 'fixed') then you run the first query listed and see which objects are affected. You can then use the second query to generate a dynamic list, modify that script to include a 'show errors' command after each compile statement and discover why each remaining invalid object would not successfully compile:

SQL> select 'alter '||object_type||' '||object_name||' compile;'
  2  from user_objects
  3  where status <> 'VALID'
  4  and object_type in  ('PACKAGE','PROCEDURE','FUNCTION','TYPE','VIEW', 'TRIGGER','SYNONYM')
  5  union
  6  select 'alter '||substr(object_type, 1, instr(object_type, ' BODY') -1)||' '||object_name||' compile body;'
  7  from user_objects
  8  where status <> 'VALID'
  9  and instr(object_type, ' BODY') > 0
 10  /

'ALTER'||OBJECT_TYPE||''||OBJECT_NAME||'COMPILE;'
--------------------------------------------------------------------------------
alter FUNCTION CHECK_SAL compile;
alter PROCEDURE RAISE_SAL compile;

SQL>

Generating a more detailed error message for each compile:

SQL> alter FUNCTION CHECK_SAL compile;

Warning: Function altered with compilation errors.

SQL> show errors
Errors for FUNCTION CHECK_SAL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
1/30     PLS-00201: identifier 'EMPLOYEES.EMPLOYEE_ID' must be declared
SQL> alter PROCEDURE RAISE_SAL compile;

Warning: Procedure altered with compilation errors.

SQL> show errors
Errors for PROCEDURE RAISE_SAL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
2/20     PLS-00201: identifier 'EMPLOYEES.EMPLOYEE_ID' must be declared
SQL>

So we're missing a table these objects depend upon, and until that table is replaced they will remain invalid, so we can stop trying to compile them.

In most cases views won't need to be recompiled as select access to invalid views automatically performs that action; of course if the situation is like that shown above nothing will make the invalid view usable.

Unusable indexes are treated a bit differently, as they can't be recompiled; they need to be rebuilt. Normally the database would be shutdown and then started in restricted mode to allow the rebuild to commence unhindered (rebuilding indexes really shouldn't be done when users are actively accessing the database as it consumes resources and can cause exceptional delays for other processes while the rebuild of each index is taking place). A similar query to the invalid objects SQL can find the unusable indexes:

SQL> select index_name, status
  2  from user_indexes
  3  where status <> 'VALID';

no rows selected

SQL>

Had there been any unusable indexes the following query will generate the necessary executable statements:

select 'alter index '||index_name||' rebuild tablespace '||tablespace_name||';'
from user_indexes
where status <> 'VALID';

Spool that output to a file, verify it wrote correctly (the default line size may be a bit short for some resulting lines, so you need to check that each alter statement is, indeed, on a single line) then prepare to execute the script after the database is in restricted mode. Log the execution of the script so any resource-related errors can be addressed before it's run again (usually, though, one run is sufficient).

Fixed views are a different story, as they're based upon memory and internal disk structures. If any of these are declared INVALID the only recommended action to be taken is to contact Oracle Support as you cannot recompile such views. It's likely that you'll be told to shutdown and startup the database, but do NOT proceed with that action until told to do so by, you guessed it, Oracle Support.

So, finding and correcting invalid database objects is fairly straightforward; it does require attention to detail, however, to ensure that all objects which can be successfuly recompiled/rebuilt are again in a usable state. Practice on a test database is recommended so that if and when this process is required on a production system it's been tested and re-tested and the method is properly defined and documented.

Of course, if it ain't broke ...