重新编译invalid objects的几种方法

1 使用alter **** compile 语句进行编译
 
2 以SYSDBA用户，执行ORACLE_HOME/rdbms/admin/utlrp.sql 脚本
 
3 用DBMS_UTILITY包来进行编译.

 

 

Raymond 在Recompiling invalid objects提到了如何有效地重新编译无效对象.提到了三种比较有效地方法:
1、利用$ORACLE_HOME/rdbms/admin下的utlrp.sql脚本编译.一般都是在迁移或者升级之后运行该脚本.Raymond说该方法的不足之处在于这个脚本是对整个数据库中的对象进行重新编译的,所以不可取.有网友指出utlrp.sql 实际上是调用utlrcmp.sql的这样就可以用utl_recomp包来做喽(这样就是比较好的方法). 
2、用DBMS_UTILITY包来进行编译.但是也有一定的局限性. 
3、Raymond提到了自己的解决办法:alter **** compile。 不过也立刻有人指出来,这样对 View 的重新编译是无能为力的(ALTER_COMPILE只能处理:PACKAGE, PACKAGE BODY,PROCEDURE, FUNCTION, TRIGGER).参见下面的脚本

 

 

 

 

 

这里暂时先介绍一下第三种方法：

 

COMPILE_SCHEMA Procedure

This procedure compiles all procedures, functions, packages, and triggers in the specified schema.

Syntax

DBMS_UTILITY.COMPILE_SCHEMA ( schema VARCHAR2, compile_all BOOLEAN DEFAULT TRUE, reuse_settings BOOLEAN DEFAULT FALSE);

Parameters

Table 116-12 COMPILE_SCHEMA Procedure Parameters

Parameter	Description
schema	Name of the schema
compile_all	If TRUE, will compile everything within the schema regardless of whether it is VALID If FALSE, will compile only INVALID objects
reuse_settings	Indicates whether the session settings in the objects should be reused, or whether the current session settings should be adopted instead

Exceptions

Table 116-13 COMPILE_SCHEMA Procedure Exceptions

Exception	Description
ORA-20000	Insufficient privileges for some object in this schema
ORA-20001	Cannot recompile SYS objects
ORA-20002	Maximum iterations exceeded. Some objects may not have been recompiled.

Usage Notes

After calling this procedure, you should select from view ALL_OBJECTS for items with status of INVALID to see if all objects were successfully compiled.

To see the errors associated with INVALID objects, you may use the Enterprise Manager command:

SHOW ERRORS .  执行方法很简单：exec dbms_utility.compile_schema('*****');即可。注意*****代表是出现invalid object的用户名称。剩下的compile_all和reuse_settings两个参数根据实际情况来选择。

 

例如：

SQL> select object_name, object_type, owner from dba_objects where status='INVALID';

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE         OWNER
------------------- ------------------------------
PROC_APPROVE_ORG_REPORT_LIST1
PROCEDURE           TJDATA

SQL> exec dbms_utility.compile_schema('TJDATA');

PL/SQL procedure successfully completed.

SQL> select object_name, object_type, owner from dba_objects where status='INVALID';

no rows selected

SQL>