Find Out Invalid Database Objects

Find Out Invalid Database Objects

The following finds out invalid database objects:

#####################################################################         
## invalid_object_alert.sh ##
#####################################################################         
#!/bin/ksh
. /etc/oracle.profile
sqlplus -s <<!
oracle/$1@$2
set          feed off
set heading off
column object_name format a30
spool invalid_object.alert         
SELECT  OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS
FROM    DBA_OBJECTS
WHERE   STATUS = 'INVALID'
ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME;
spool off         
exit
!
if [ `cat invalid_object.alert|wc -l` -gt 0 ]
then
    mailx -s "INVALID OBJECTS for ${2}" $DBALIST < invalid_object.alert
fi
$ cat invalid_object.alert

OWNER        OBJECT_NAME           OBJECT_TYPE          STATUS
----------------------------------------------------------------------
HTOMEH       DBMS_SHARED_POOL            PACKAGE BODY          INVALID
HTOMEH       X_$KCBFWAIT                 VIEW                  INVALID
IMON         IW_MON                      PACKAGE               INVALID
IMON         IW_MON                      PACKAGE BODY          INVALID
IMON         IW_ARCHIVED_LOG             VIEW                  INVALID
IMON         IW_FILESTAT                 VIEW                  INVALID
IMON         IW_SQL_FULL_TEXT            VIEW                  INVALID
IMON         IW_SYSTEM_EVENT1            VIEW                  INVALID
IMON         IW_SYSTEM_EVENT_CAT         VIEW                  INVALID
LBAILEY      CHECK_TABLESPACE_USAGE      PROCEDURE             INVALID
PATROL       P$AUTO_EXTEND_TBSP          VIEW                  INVALID
SYS          DBMS_CRYPTO_TOOLKIT         PACKAGE               INVALID
SYS          DBMS_CRYPTO_TOOLKIT         PACKAGE BODY          INVALID
SYS          UPGRADE_SYSTEM_TYPES_TO_816 PROCEDURE             INVALID
SYS          AQ$_DEQUEUE_HISTORY_T       TYPE                  INVALID
SYS          HS_CLASS_CAPS               VIEW                  INVALID
SYS          HS_CLASS_DD                 VIEW                  INVALID