1.查找失效的包

a.统计

select owner, object_type, status, count(*)

  from dba_objects

 where status = 'INVALID'

 group by owner, object_type, status

 order by owner, object_type;

b.具体

select owner, object_name, object_type, status

  from dba_objects

 where status = 'INVALID'

 order by 1, 2, 3;

2.重新编译

a.utlrp.sql

执行@$ORACLE_HOME/rdbms/admin/utlrp.sql脚本编译数据库失效对象

b.dbms_utility.compile_schema

ORACLE提供了自动编译的接口 dbms_utility.compile_schema(user,false); 

调用这个过程就会编译所有失效的过程、函数、触发器、包

exec dbms_utility.compile_schema( 'SCOTT' )

c.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 ('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('编译' || v_objtype || ' ' || v_objname || '()成功'); 

    EXCEPTION 

      WHEN OTHERS THEN 

        dbms_output.put_line('编译' || v_objtype ||' ' || v_objname || '()失败.' || SQLERRM); 

    END; 

 END LOOP; 

 CLOSE cur; 

END;