Oracle 编译用户无效对象

一、查询无效对象的方法：

1.统计失效对象

select owner, object_type, status, count(*)
from dba_objects
  where status='INVALID'
group by owner, object_type, status
order by owner, object_type

2.查看具体失效对象

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;

column owner format a15
column object_name format a25
column object_type format a15
SELECT owner,
       object_name,
       object_type,
       status
FROM   dba_objects
WHERE  status != 'VALID'
       AND owner != 'SYS'
       AND owner != 'SYSTEM';

3.查看失效对象的依赖关系

col owner for a30
col name for a30
col type for a18
col referenced_owner for a20
col referenced_name for a30
col referenced_type for a18
col dependency_type for a4
set line 300
select OWNER,NAME,TYPE,REFERENCED_OWNER,REFERENCED_NAME,REFERENCED_TYPE,DEPENDENCY_TYPE from dba_dependencies where OWNER='SCOTT' AND NAME='V_TEST';

二、编译无效对象的方法：

1.使用ALTER * COMPLIE语句手工进行编译，这个适用于少数、个别对象失效

    1）生成手动编译无效对象脚本
    select  'ALTER ' || OBJECT_TYPE || ' ' ||  OWNER || '.' || OBJECT_NAME || ' COMPILE;' 
    from dba_objects where status = 'INVALID' and object_type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE','TRIGGER','VIEW','SYNONYM','JAVA SOURCE','JAVA CLASS');

    2)手动执行
    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.执行@$ORACLE_HOME/rdbms/admin/utlrp.sql脚本编译数据库失效对象。

许多情况下，由于数据库的升级或迁移，会导致数据库中的对象失效。由于对象之间可能存在复杂的倚赖关系，所以手工编译通常无法顺利通过。通常我们会在Oracle的升级指导中看到这个脚本，Oracle强烈推荐在migration/upgrade/downgrade之后，通过运行此脚本编译失效对象。但是注意,Oracle提醒，此脚本需要用SQLPLUS以SYSDBA身份运行，并且当时数据库中最好不要有活动事物或DDL操作，否则极容易导致死锁的出现。
另外，utlrp.sql 里面其实调用了$ORACLE_HOME/rdbms/admin/utlrcmp.sql来编译失效对象

 

3.ORACLE提供了自动编译的接口dbms_utility.compile_schema(user,false); 调用这个过程就会编译所有失效的过程、函数、触发器、包

exec dbms_utility.compile_schema( 'SCOTT' );

4.在SQL*plus中利用中间脚本编译

1）创建脚本reCompile.sql

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 OWNER = 'APPS'
and object_type in ('PACKAGE','FUNCTION','PROCEDURE','TRIGGER','JAVA SOURCE','JAVA CLASS','VIEW','SYNONYM');
select 'alter package ' || owner || '.' || object_name ||
' compile body;'
from dba_objects
where status = 'INVALID'
and OWNER = 'APPS'
and object_type in ('PACKAGE BODY');
spool off;
set heading on;
set feedback on;
set echo on;
@run_invalid

2）在SQL*Plus中执行
格式：@文件所在本地路径/文件名
注：路径不能有中文
SQL>@/home/oracle/reCompile.sql;
备注：运行脚本reCompile.sql的时候，会创建另一个脚本run_invalid，紧跟着执行该脚本，完成编译工作

 

5.编写PL/SQL利用游标编译

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 ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE','TRIGGER','VIEW','SYNONYM','JAVA SOURCE','JAVA CLASS'); 
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('编译' || v_objtype || ' ' || v_objname || '()成功'); 
    EXCEPTION 
      WHEN OTHERS THEN 
        dbms_output.put_line('编译' || v_objtype ||' ' || v_objname || '()失败.' || SQLERRM); 
    END; 
 END LOOP; 
 CLOSE cur; 
END;
/

注意：视图，存储过程，函数、包等，如果代码本身没有什么错误，只是引用的对象发生了变化。也会失效。但并不影响调用，因为ORACLE在调用时会自动重新编译的，如果其它对象变化后导致编译有错误。这时调用时重新编译后也是错误并处于失效状态，所以调用会出错。