Oracle数据库对象编译报错

检查是否存在失效对象。

SQL> select t.owner, t.object_type, t.object_name, t.status from dba_objects t where t.status = 'INVALID';
系统显示如下类似信息说明不存在失效对象。
no rows selected

 重新编译失效对象。

请执行如下命令获取失效对象的编译语句。

SQL> select 'alter PACKAGE '|| owner || '.' || object_name || ' compile BODY;' from dba_objects where status = 'INVALID' and object_type='PACKAGE BODY' union all select 'alter '|| owner || ' ' || object_type || ' ' || object_name || ' compile;' from dba_objects where status = 'INVALID' and object_type='SYNONYM' and OWNER='PUBLIC' union all select 'alter ' || object_type || ' ' || owner || '.' || object_name || ' compile;' from dba_objects where status = 'INVALID' and object_type<>'PACKAGE BODY' and not(object_type='SYNONYM' and OWNER='PUBLIC');

 

对于系统用户对象，编译的时候可能会存在编译失败的情况，如：

SQL> alter PACKAGE SYSTEM.DBMS_REPCAT_AUTH compile BODY;
Warning: Package Body altered with compilation errors.

 

报错后，我们可以紧接着通过show errors获取详细的失败信息：

SQL> show errors;
Errors for PROCEDURE GRANT_OBJECTS_RIGHT_SQL:

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/5      PL/SQL: Statement ignored
9/16     PLS-00201: identifier 'DBMS_SQL' must be declared
10/5     PL/SQL: Statement ignored
10/224   PLS-00201: identifier 'DBMS_SQL' must be declared
11/5     PL/SQL: Statement ignored
11/5     PLS-00201: identifier 'DBMS_SQL' must be declared
12/5     PL/SQL: Statement ignored
12/15    PLS-00201: identifier 'DBMS_SQL' must be declared
14/5     PL/SQL: Statement ignored
14/11    PLS-00201: identifier 'DBMS_SQL' must be declared
24/6     PL/SQL: Statement ignored

LINE/COL ERROR
-------- -----------------------------------------------------------------
24/6     PLS-00201: identifier 'DBMS_SQL' must be declared

系统对象比较常见的报错信息都是由于引用的包或过程没有权限导致，这个很可能是由于安全加固导致公共权限被收回。

对于上面报错的DBMS_SQL需要被声明，这个比较常见，默认情况下DBMS_SQL是public权限，可以直接授权回来：

SQL>grant execute on sys.DBMS_SQL to public;

基于安全加固的考虑，也可将执行权限授予报错的系统用户：

SQL>grant execute on sys.DBMS_SQL to system;

再重新编译失效的系统对象，如还有其他的包或存储过程报错，继续授权再进行编译，基本都能编译通过。