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
Tuesday, January 13, 2009
Identifying and compiling invalid objects in Oracle applications:
Labels:
COMPLING,
DBA_OBJECTS,
IDENTIFYING,
INVALID,
OBJECTS,
RECOMPILING
Subscribe to:
Post Comments (Atom)
1 comment:
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
Post a Comment