In this Document
  Purpose
  Scope and Application
  Debug and Validate Invalid Objects
     A) Find Invalid Objects:
     B) Try Manual method of validation:
     C) How to Find the Compilation Errors:
     D) Find Dependencies & Debug error message:
     E) Recreate sys/system objects using scripts
     F) Standard scripts for automatic recompilation of invalid objects
     More debugging
     G)Enable 10046 tracing and upload trace.
     H) Errorstack for specific errors:
     Information required by Support
  References

Applies to:

Purpose

This document is intended to help customers in resolving invalid database objects problem.

Scope and Application

This can be used to resolve / generate more debug information and contact support for resolving the problem.

This note covers basic database objects like views,procedures and packages.

It does not deal much in detail with invalid java objects.Articlesreferred in References section can help in resolving invalid javaobjects.

Debug and Validate Invalid Objects

A) Find Invalid Objects:

1.To Find the number of invalid objects :

select count(*) from dba_objects where status=’INVALID’;

select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type;

2.To identify the object name and their types and owner:

select owner, object_name,object_type from dba_objects where status =’INVALID’;

B) Try Manual method of validation:

Alter procedure <owner>.<procedure_name> compile;

Alter function <owner>.<function_name> compile;

Alter view <owner>.<view_name> compile;

Alter package <owner>.<package_name> compile;

Alter package <owner>.<package_name> compile body;

Alter materialized view <owner>.<Package_name> Compile;

In case you have lots of invalid objects,you can generate scripts that will

generate the sqls for compiling the invalid objects :

In sqlplus connect as sys:

set heading off

spool compileinvalid.sql

select 'alter '||object_type|| ' ' || owner ||'.'||object_name || ' compile;' from dba_objects where status='INVALID';

spool off

Then run compileinvalid.sql in sqlplus prompt as sys user..

To compile invalid package body use:

alter package <package_name> compile body;

C) How to Find the Compilation Errors:

If any of compile statement returns warning of form:

Warning: Procedure created with compilation errors.
Warning: View created with compilation errors.
Warning: Function created with compilation errors.
Warning: Package created with compilation errors.

C.1)From sqlplus

Type in sqlplus

Show errors

Immediately following the failing alter .. compile statement.And this should show the cause of compilation error.

Note the error number and the error description and resolve the error.

C.2) From data dictionary view : dba_errors

If show errors returns no errors,then check in dba_errors .

Select text from dba_errors where name =’<Object_name>' and owner =’<owner_name>’;

The above select would give the list of error messages that led to compilation error.

D) Find Dependencies & Debug error message:

If the error message is generic like ,table or view does not exist/nocolumns in table ,then to debug further we need to find all the objectson which the invalid object is dependent on.

We can get the information using the following query:

Select referenced_owner, referenced_name,referenced_type from
dba_dependencies where name=’<Object_name>’ and type =‘<Object_type>’ and owner =’<owner_name>’;

Check for the existence of the dependent objects and their status by querying dba_objects .

E) Recreate sys/system objects using scripts

If they are sys/system related packages/procedures/views/functions,youcan recreate them using the scripts in $ORACLE_HOME/rdbms/admin.

To find the creation scripts ,say for example dbms_shared_pool

cd $ORACLE_HOME/rdbms/admin grep -i “create or replace package dbms_shared_pool” *.sql

Then run the script in sqlplus as “sys” user.

F) Standard scripts for automatic recompilation of invalid objects

In addition to manual recompilation,you have scripts to validate packages/sub programs

The script has to be run in restricted mode of database as sysuser.Note the number of invalid objects and spool the invalid objects list before and after running these scripts.

More debugging

In certain cases, the structure of base table could have changed fromone version to another version,and this could be the cause of invalidobjects.This can be seen after upgrade.The following tracing would helpin debugging such cases.

G)Enable 10046 tracing and upload trace.

If utlrp and manual method of compilation fails and still unable tosolve the problem by using debugging steps in C,D,E sections then :

Alter session set events ‘10046 trace name context forever,level 12’

Alter procedure <owner.procedure_name> compile;

Exit session.

Check in user_dump_dest for trace file generated. Upload this to Oracle Support for further analysis.

H) Errorstack for specific errors:

If you find any specific errors ,you may also get an error stack:

Alter session set events ‘nnn trace name errorstack level 3’;

nnn=> Error number

this will also generate trace in user_dump_dest.

For example,say you get Ora-00942 as one of the errors:

Then you have to set errorstack :

Alter session set events ‘942 trace name errorstack level 3’;

Information required by Support

If you are unable to resolve the problem still,then please contact support with the following details:

1.Invalid objects info created by following script.

The above script is uploaded as check_invalids.sql to this note as an attachment.

Run this sql file as sys user and upload the following spool files created  in the current working directory:

INVALID_OBJECTS_AND_REGISTRY_INFO.lst

INVALID_OBJECTS.lst

2.Trace files generated in Steps G & H.

3.List of dependent objects for every invalid object.

Select referenced_owner, referenced_name,referenced_type fromdba_dependencies where name=’<Object_name>’ and type =‘<Object_type>’ and owner =’<owner_name>’;

4.Error messages obtained from methods suggested in Step C.

References