--执行以下SQL语句，可查询Procedure,Function,Package,Package Body,Trigger,View是否失效:
SELECT * FROM dba_objects a WHERE a.owner IN ('SYS'，'SYSTEM','ZLTOOLS','ZLHIS') AND a.status='INVALID';
--直接生成失效的对象的编译方式：
SELECT 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;'
FROM ALL_OBJECTS
WHERE STATUS = 'INVALID'
AND OBJECT_TYPE IN ('PROCEDURE','FUNCTION', 'VIEW','TRIGGER','PACKAGE','PACKAGE BODY')
AND OWNER IN ('SYS'，'SYSTEM','ZLTOOLS','ZLHIS');
SELECT 'ALTER PACKAGE ' || OWNER || '.' || OBJECT_NAME || ' COMPILE BODY;' FROM ALL_OBJECTS WHERE STATUS = 'INVALID' AND OBJECT_TYPE = 'PACKAGE BODY';
--查看失效的index
SELECT * FROM Dba_Indexes a WHERE a.status='UNUSABLE' ;

ITPub博客

如何查看失效的对象