Tuesday, January 13, 2009

Identifying and compiling invalid objects in Oracle applications:

Invalid objects can occur for many reasons. You will usually find invalid objects after upgrading, or applying patches Invalid objects are usually caused by missing grants, synonyms, views, tables or packages, but can also be caused by corrupted packages.
You should normally use DBA_OBJECTS for querying the invalid objects because it returns every object in the Database.


Use this command to find the number of invalid objects in database.

SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS = 'INVALID';

For a more detailed query, use the following script:

SELECT OWNER, OBJECT_TYPE, COUNT(*) FROM DBA_OBJECTS WHERE STATUS = 'INVALID' GROUP BY OWNER, OBJECT_TYPE;

There are different ways to recompile invalid objects in schema.
1. DBMS_DDL

2. DBMS_UTILITY
3. UTL_RECOMP
4. UTLRP.SQL

DBMS_DDL
Syntax: Exec dbms_ddl.alter_compile(type,schema,name)
Where

Type : Must be procedure,function,package or package body.
Schema : Database username.
Name: Object name
Example:

Sql> exec dbms_ddl.alter_compile ('PROCEDURE','SCOTT','TEST');
PL/SQL procedure successfully completed.

DBMS_UTILITY:

This compiles the object in the specified schema.
Syntax

Exec dbms_utility.compile_schema ( schema,compile all)
Schema : Database Username

Compile All : Object type ( procedure, function, packages,trigger)
Example

SQL> exec dbms_utility.compile_schema('SCOTT');
PL/SQL procedure successfully completed

UTL_RECOMP
Syntax

Exec UTL_RECOMP.RECOMP_SERIAL ();
Example

SQL> Exec UTL_RECOMP.RECOMP_SERIAL ();
PL/SQL procedure successfully completed
Note: Required SYS user to run this package

UTLRP.SQL
Syntax

Located: $ORACLE_HOME/rdbms/admin
Example:

Sql> @$ ORACLE_HOME/rdbms/admin/utlrp.sql

Objects that requires recompilation are:VIEW, SYNONYM, PUBLIC SYNONYM, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, UNDEFINED , JAVA CLASS, TYPE, TYPE BODY

1 comment:

diana_ser said...

looks great. The parsing of corrupted documents can be also performed by the recovery sql server 2005 utility, it is the fastest way to open affected files