This code will check for invalid stored objects in the database (views, PL/SQL, etc..) and then try to re-compile all invalid code.
REM LOCATION: Object Management\Functions,Procedures, and Packages
REM FUNCTION: Report on and recompile invalid objects
REM TESTED ON: 10.2.0.3 and 11.1.0.6
REM PLATFORM: non-specific
REM REQUIRES: utlrp.sql (in $OH/rdbms/admin)
REM
REM
REM ******************** Knowledge Xpert for Oracle Administration ********************
REM
REM NOTES: run from SYSDBA account only!
REM
REM ***********************************************************************************
COLUMN owner format a30
COLUMN object_name format a30 wrap
COLUMN object_type format a30 wrap
SET pages 56 lines 130 feedback off echo off
TTITLE "Report of Invalid Objects In Database" skip 2
SELECT owner, object_name, object_type
FROM dba_objects
WHERE status = 'INVALID';
PROMPT "Will now run utlrp.sql to try to recompile objects"
@?/rdbms/admin/utlrp.sql
