Invalid objects and re-compiled

Find invalid objects and re-compiled.

#!/bin/ksh
ORACLE_HOME=/prod/ora920/db/9.2.0;export ORACLE_HOME
ORACLE_SID=PROD;export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH;export PATH
sqlplus -s " / as sysdba" >/prod/ora920/dba/invalid_obj_bf.log << "EOF" set pagesize 0; set linesize 2048; set head off; set feedback off; select owner,object_name,object_type,last_ddl_time from sys.dba_objects where status='INVALID' and OBJECT_TYPE not in('MATERIALIZED VIEW'); exit; EOF IOBJ=`cat /prod/ora920/dba/invalid_obj_bf.log | wc -l` if [ $IOBJ -gt 0 ] then sqlplus -s " / as sysdba" >/prod/ora920/dba/ora_invalid_obj.sql << "EOF" set pagesize 0; set linesize 2048; set head off; set feedback off; select decode(OBJECT_TYPE, 'PACKAGE BODY','alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;','alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;') from dba_objects a where OBJECT_TYPE in ('PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'VIEW') and status='INVALID'; exit; EOF sqlplus -s " / as sysdba" << "EOF" @/prod/ora920/dba/ora_invalid_obj.sql exit; EOF mail -s "Oracle PROD invalid objects auto-recompiled information" xxx@xx.com.tw < /prod/ora920/dba/invalid_obj_bf.log fi