SET HEADING OFF
SET FEEDBACK OFF;
SPOOL C:INVALID.LIS
SELECT
'ALTER '||DECODE(OBJECT_TYPE,'PACKAGE BODY','PACKAGE',OBJECT_TYPE)
||' '||OWNER||'.'||OBJECT_NAME||' COMPILE'||
DECODE(OBJECT_TYPE,'PACKAGE BODY',' BODY','PACKAGE',' PACKAGE','')||';'
FROM
DBA_OBJECTS
WHERE
OBJECT_TYPE IN
('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'TRIGGER', 'VIEW') AND
OWNER NOT IN ('SYS','SYSTEM') AND
STATUS = 'INVALID'
ORDER BY
OWNER,
OBJECT_TYPE,
OBJECT_NAME
;
SPOOL OFF;
SPOOL C:INVALID.LOG
@C:INVALID.LIS
SPOOL OFF;
EXIT;