Oracle compile Failed Object

In the daily database maintenance process, we will find some objects in the database (package package, stored procedure procedure, function functions, view views, synonyms ...). ) is invalidated and renders an invalid state (INVALID). Sometimes it is necessary to periodically check the database in which failed objects, for objects with exceptions need to be recompiled, some automatically invalidated objects, usually in the next call, will be recompiled, so these do not need human intervention. So why does the object suddenly fail? How quickly and efficiently compile the failed object? What failed objects do not require us to recompile?

Database object Invalidation Reason

There are a number of reasons why database objects are invalidated, and some common causes are summarized below (some of them are missing, I hope you will add):

1: When the structure of the referenced object changes, the dependent object is changed to the invalid state.

The objects in the database (stored procedures, functions, packages, views, triggers, which often need to refer directly or indirectly to other objects, the object's dependencies include direct and indirect two kinds, in which direct dependency refers to the storage object directly dependent on the referenced object, whereas indirect dependency refers to the object being indirectly dependent on the referenced object

To see the referenced object, you can view it from the following SQL

SELECT * from dba_dependencies where name= ' &objectname ';

SELECT * from all_dependencies where name= ' &objectname ';

SELECT * from user_dependencies where name= ' &objectname ';

For a simple example, a view v_test references a table test,test table structure, which causes the view V_test to become an invalid object.

Sql> CREATE TABLE TEST (ID number (10));

Table created.

Sql> CREATE VIEW v_test as SELECT * from TEST;

View created.

Sql> SELECT object_name, STATUS from dba_objects WHERE object_name= ' v_test ';

object_name STATUS

------------------- ----------------

V_test VALID

--Modify the table structure, add a field name, view v_test becomes invalid

sql> ALTER TABLE TEST ADD NAME VARCHAR (12);

Table altered.

Sql> SELECT object_name, STATUS from dba_objects WHERE object_name= ' v_test ';

object_name STATUS

------------------- ----------------

V_test INVALID

--After Query view v_test, the database will recompile the view

Sql> SELECT * from V_test;

No rows selected

Sql> SELECT object_name, STATUS from dba_objects WHERE object_name= ' v_test ';

object_name STATUS

------------------- ----------------

V_test VALID

In fact, regardless of views, such as stored procedures, functions, packages, and so on, if the code itself does not have any errors, but the object of reference has changed. Will fail. However, the invocation is not affected because Oracle is automatically recompiled when called, and if the other objects change, the compilation has errors. When the call is recompiled, it is also an error and is in a failed state, so the call can be faulted.

2: When the SQL script is published (package, stored procedure, function, etc.), there is no sufficient testing, compile-time error, when the object becomes invalid.

3: Database upgrades, migrations, there are a large number of invalid objects (intrinsic reason, individual conjecture boils down to reason 1).

4: For example, Oracle automatically maintains the partitioning index, and for global indexes, if you do not specify update index when you manipulate the partitioned table, the global index is invalidated and needs to be rebuilt.

Methods of compiling failed objects

Statistics failed objects:

Select owner, object_type, Status, COUNT (*)

From Dba_objects

where status= ' INVALID '

Group by owner, Object_type, status

Order by Owner, Object_type

View specific failed objects

Col owner for A20;

Col object_name for A32;

Col object_type for A16

Col Status for A8

Select owner, object_name, object_type, status

From Dba_objects

where status= ' INVALID '

Order by 1, 2, 3;

Most of the following reference Bowen Oracle compiler invalid objects commonly used methods, modified and summarized, sorted

1: Use ALTER * * * * * * Complie Statement Manual compilation, this applies to a few, individual object invalidation

Alter package <schema name>.<package_name> compile;

Alter package <schema name>.<package_name> compile body;

Alter view <schema name>.<view_name> compile;

Alter TRIGGER <schema) .<trigger_name> compile;

2: Execute @ $ORACLE _home/rdbms/admin/utlrp.sql script Compile database failed object.

In many cases, an object in the database is invalidated due to a database upgrade or migration. Manual compilation does not usually pass smoothly because of the potentially complex dependency relationships between objects. Usually we see this script in Oracle's Upgrade guide, and Oracle strongly recommends that you compile the failed object by running this script after Migration/upgrade/downgrade. Note, however, that the Oracle reminder that this script needs to run with Sqlplus as SYSDBA, and that the database is best not to have activities or DDL operations, it is very easy to cause deadlocks (which is easy to understand).

Oracle highly recommends running this script towards the "end of" any of the migration/upgrade/downgrade.

In addition, utlrp.sql inside actually called $oracle_home/rdbms/admin/utlrcmp.sql to compile the invalid object.

3:oracle provides an automatically compiled interface Dbms_utility.compile_schema (USER,FALSE); Calling this procedure compiles all failed procedures, functions, triggers, packages

exec dbms_utility.compile_schema (' SCOTT ')

4: Some users write the compilation of invalid objects (after finishing)

SQL 1: Compiling failed objects

Set heading off;

Set feedback off;

Set echo off;

Set lines 999;

Spool Run_invalid.sql

Select

' ALTER ' | | object_type | | ' ' ||

OWNER | | '.' || object_name | | ' COMPILE; '

From

Dba_objects

where

Status = ' INVALID '

and

Object_type in (' PACKAGE ', ' FUNCTION ', ' PROCEDURE ', ' TRIGGER ', ' Java SOURCE ', ' Java CLASS ', ')

;

Spool off;

Set heading on;

Set feedback on;

Set echo on;

@run_invalid. sql

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

SQL 2: In the above method, only know that So-and-so compilation failed, not clear the reason for failure, you can use Pl/sql to implement more detailed error information

DECLARE

V_objname User_objects.object_name%type;

V_objtype User_objects.object_type%type;

CURSOR cur is

SELECT Object_name,object_type

From User_objects

WHERE status = ' INVALID '

and object_type in (' FUNCTION ', ' Java SOURCE ', ' Java CLASS ', ' PROCEDURE ', ' PACKAGE ', ' TRIGGER ');

BEGIN

OPEN cur;

LOOP

FETCH cur into v_objname, v_objtype;

EXIT when Cur%notfound;

BEGIN

EXECUTE Immediate ' Alter ' | | V_objtype | | ' ' || v_objname| | ' Compile ';

Dbms_output.put_line (' Compiling ' | | | v_objtype | | ' ' || V_objname | | ' () success ');

EXCEPTION

When others THEN

Dbms_output.put_line (' Compiling ' | | v_objtype | | ' || V_objname | | ' () failed. ' | | SQLERRM);

End;

End LOOP;

Close cur;

End;

Source: http://www.cnblogs.com/kerrycode/