#!/usr/bin/ksh
#whatinvalid19c ()
#------------------------------------------------------------------------------#
#    Module Name:  whatinvalid19c                                              #
#                                                                              #
#    Purpose:      Report on the Invalid Objects by Schama.                    #
#                    ADMIN-Server    ConcMgr-Server                            #
#                    Forms-Server    Web-Server                                #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    10-Oct-2009   M.Barone      Module design/creation                        #
#------------------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $ORACLE_HOME | cut -f3 -d'/')
        HNAME=$(uname -n)

        clear
        echo 
        echo  " #--------------------------------------------------------#"
        echo  " # whatinvalid19c:            Oracle Apps Invalid Objects #"
        echo  " #--------------------------------------------------------#"
        echo                                                               
        clear

#------------------------------------------------------------------------------#
#  Initialize                                                                  #
#------------------------------------------------------------------------------#
        SPOOLNM=/tmp/whatinvalid19c_$$.txt

#------------------------------------------------------------------------------#
#  Oracle19c                                                                   #
#------------------------------------------------------------------------------#
       export ORACLE_PDB_SID=$ORASID

#------------------------------------------------------------------------------#
#  SQL*Plus ("Here-Document")                                                  #
#------------------------------------------------------------------------------#
sqlplus  -s  /nolog  <<-END_FILE

        CONNECT / as SYSDBA 

        SPOOL ${SPOOLNM}

        clear   BREAKS
        clear   BUFFER
        clear   COLUMNS
        clear   COMPUTES
        clear   SCREEN
        clear   SQL
        clear   TIMING

        set     feedback        off
        set     flush           on
        set     heading         on
        set     pause           off
        set     space           1
        set     termout         on
        set     verify          on
        set     linesize        80
        set     pagesize        999

        prompt #---------------------------------------------------------------#
        prompt #- What (whatinvalid19c)                                       -#
        prompt #---------------------------------------------------------------#

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        BREAK   ON  REPORT
        BREAK   ON  owner          skip 2       ON REPORT
        COMPUTE SUM LABEL 'Total'  OF  objcnt   ON OWNER
        COMPUTE SUM LABEL 'Total'  OF  objcnt   ON REPORT

        COLUMN  OWNER           format  A10     Heading "Owner!Schema"
        COLUMN  OBJECT_NAME     format  A40     Heading "Object Name"
        COLUMN  OBJECT_TYPE     format  A20     Heading "Object Type"
        COLUMN  OBJCNT          format  999999999       Heading "Count" 

        TTITLE  CENTER  '${ORASID} (${HNAME}) Apps Invalid Objects'             -
                RIGHT   'Page:'         FORMAT 999 SQL.pno              skip 1  -
                CENTER  'whatinvalid19c -- Sorted by Schema Name'               skip 2

        alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

        SELECT  DISTINCT owner, object_type, count(1) objcnt
          FROM  dba_objects
         WHERE  STATUS <>  'VALID'
        GROUP   BY owner, object_type
        ORDER   BY 1,2;

END_FILE


echo
echo  "Please see  /tmp/whatinvalid19c_$$.txt"
}