Summary of ORACLE compilation failure objects

During routine database maintenance, we will find some objects in the database (Package, stored Procedure, Function, View, synonym .....) it becomes INVALID and is in an INVALID status (INVALID ). Sometimes it is necessary to regularly check which failed objects exist in the Database and re-compile the objects with exceptions. Some automatically invalid objects will be re-compiled at the next call, therefore, manual intervention is not required. Why is the object suddenly invalid? How to quickly and efficiently compile invalid objects? Which invalid objects do not need to be re-compiled?

Database Object failure cause

There are many reasons for database object failure. The following describes some common causes (some of which may be omitted. I hope you can add them ):

1: when the structure of the referenced object is changed, the dependent object will be changed to the INVALID state.

Objects (stored procedures, functions, packages, views, and triggers) in the database often need to reference other objects directly or indirectly. Object dependencies include direct and indirect, direct dependency means that the storage object is directly dependent on the referenced object, while indirect dependency means that the object is indirectly dependent on the referenced object.

To view the referenced object, you can view it using the following SQL statement:

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

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

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

For example, if view V_TEST references table TEST and table TEST modifies the table structure, the view V_TEST becomes invalid.

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. After a field NAME is added, the 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 querying the view V_TEST, the database recompiles 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, no matter the view, such as stored procedures, functions, and packages, if the Code itself has no errors, the referenced object is changed. It will also become invalid. But it does not affect the call, because ORACLE will automatically re-compile during the call, if other objects change, causing compilation errors. After the call is re-compiled, it is also an error and is invalid. Therefore, an error occurs during the call.

2: When an SQL script (package, stored procedure, function, etc.) is released, it is not fully tested, and an error occurs during compilation. In this case, the object becomes invalid.

3: a large number of invalid objects occur during database upgrades and Migration (the root cause is personal speculation due to cause 1 ).

4: for example, Oracle automatically maintains the partition index. For a global index, if no update index is specified during operations on the partition table, the global index becomes invalid, need to be rebuilt.

 

Methods for compiling invalid objects

Invalid statistical 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 invalid 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 sections refer to the common methods for compiling invalid objects in the blog article Oracle, and make modifications, summary, and collation.

 

1:Use the ALTER *** COMPLIE statement for manual compilation. This applies to the failure of a few or some objects.

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:Run the @ $ ORACLE_HOME/rdbms/admin/utlrp. SQL script to compile the database invalidation object.

In many cases, the objects in the Database become invalid due to database upgrades or migration. Because objects may have complex dependency relationships, manual compilation usually fails. We usually see this script in Oracle's upgrade guide. Oracle strongly recommends compiling invalid objects by running this script after migration/upgrade/downgrade. But note: Oracle reminds you that this script needs to run as SYSDBA using SQLPLUS, and it is best not to have any activity or DDL operations in the database at that time, otherwise, it will easily lead to deadlocks (which is easy to understand ).

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

In addition, utlrp. SQL actually calls $ ORACLE_HOME/rdbms/admin/utlrcmp. SQL to compile invalid objects.

 

3:ORACLE provides the automatic compilation interface dbms_utility.compile_schema (user, false). Calling this process will compile all invalid processes, functions, triggers, and packages.

Exec dbms_utility.compile_schema ('Scott ')

 

4:Invalid compilation objects written by some netizens (sorted)

SQL 1: Compilation of invalid 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  

SQL 2: In the above method, you can only know the cause of a compilation 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 ('compile '| v_objtype | ''| v_objname |' () SUCCESS ');

    EXCEPTION 

      WHEN OTHERS THEN 

Dbms_output.put_line ('compilation' | v_objtype | ''| v_objname | '() failed.' | SQLERRM );

    END; 

 END LOOP; 

 CLOSE cur; 

END; 

 

References:

Http://jzhil2004.blog.163.com/blog/static/275585042010117113214172/

Http://blog.csdn.net/tianlesoftware/article/details/4843600

Http://www.233.com/oracle/jishu/20071014/101911246.html