#!/usr/bin/ksh
#whatSnapShotToOld ()
#------------------------------------------------------------------------------#
#    Module Name:  whatSnapShotTooOld                                          #
#                                                                              #
#    Purpose:      Report on the Database Environment when SnapShot Too Old.   #
#                                                                              #
#                  ORA-01555 Error Message:                                    #
#                  When Segment Number appears in ORA-01555 Error Message      #
#                  then the Database UNDO segment caused the Error.            #
#                  When there is no Segment Number in the ORA-01555 Error,     #
#                  then ORA-01555 is due to Database read consistency.         #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    12-Sep-2023   M.Barone      Module design/creation                        #
#------------------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)

        #----------------------------------------------------------------------#
        #  TEST:  Number ($#) of arguments passed to this function?            #
        #----------------------------------------------------------------------#

        if      [ $# = 0 ]
        then
            clear
            echo
            echo  " #--------------------------------------------------------#"
            echo  " # whatSnapShotTooOld              ORA-1555 Error Details #"
            echo  " #--------------------------------------------------------#"
            echo

            #------------------------------------------------------------------#
            #  Trap:  Reset CNTL-C CNTL-D CNTL-\ After Password Protection     #
            #------------------------------------------------------------------#
            trap        2
            trap        3

            read DATIM1?"        SnapShotTooOld  (FROM) Date DD-MON-YY: "
            read DATIM2?"        SnapShotTooOld  (TO)   Date DD-MON-YY: "

            if [[ $DATIM1 == '' ]]
            then
               DATTIM1=$(date '+%d-%b-%y')
            fi

            if [[ $DATIM2 == '' ]]
            then
               DATTIM2=$(date '+%d-%b-%y')
            fi

            clear
        else
            clear
        fi

        clear
        echo
        echo  " #--------------------------------------------------------#"
        echo  " # whatSnapshotTooOld:             ORA-1555 Error Details #"
        echo  " #--------------------------------------------------------#"
        echo
        echo  " Connecting as SYSDBA"
        sleep 2

        clear
        ORANAM=${ORASID}

        SPOOLNM=/tmp/whatSnapShotToOld_$$.txt

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

        CONNECT /  AS SYSDBA

        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

        SPOOL $SPOOLNM

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR
        COLUMN  begtim  FORMAT  A10          Heading 'Begin|DateTime'
        COLUMN  begtim  FORMAT  999          Heading 'Instance|ID'
        COLUMN  expcnt  FORMAT  99999999999  Heading 'UNXPSTEALCNT|Unexpired|Stolen|Count'
        COLUMN  expstl  FORMAT  9999999999   Heading 'EXPSTEALCNT|Expired|ReUsed|Count'
        COLUMN  oldcnt  FORMAT  9999999999   Heading 'SSOLDERRCNT|ORA-1555|Count'
        COLUMN  nospac  FORMAT  999999999999 Heading 'NOSPACEERRCNT|Out-Of-Space|Error|Count'
        COLUMN  maxque  FORMAT  9999999999   Heading 'MAXQUERYLEN|Max-Query|Length'

        TTITLE  CENTER  'Oracle SnapShot Too Old - ORA-1555 Error Details'  -
                LEFT    DATEVAR                                             -
                RIGHT   'Page:'         FORMAT 999 SQL.pno      skip 1      -
                CENTER  'whatsgafree -- Sorted by Component'                -
                LEFT    TIMEVAR                                 skip 2

        SELECT  TO_CHAR(sysdate, 'DD-MON-YY')                   datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')                  timevalue,
                TO_CHAR(begin_time, 'DD-MON-YY HH24:MI:SS')     begtim,
                UNXPSTEALCNT                                    expcnt,
                EXPSTEALCNT                                     expstl,
                SSOLDERRCNT                                     oldcnt,
                NOSPACEERRCNT                                   nospac,
                MAXQUERYLEN                                     maxque
        FROM    gv\$undostat
        WHERE   BEGIN_TIME between
                TO_DATE('$DATIM1','DD-MON-YY')
                and
                TO_DATE('$DATIM2','DD-MON-YY')
        ORDER BY inst_id, begin_time;

  prompt
  prompt ======================================================================
  prompt = ORA-01555 Error Message:                                           = 
  prompt =           When Segment Number appears in ORA-01555 Error Message   =
  prompt =           Database UNDO Segment caused the Error.                  = 
  prompt =           When there is no Segment Number in the ORA-01555 Error,  = 
  prompt =           then ORA-01555 is due to Database read consistency.      = 
  prompt ======================================================================

        EXIT 
END_FILE

print
print "Please see /tmp/whatSnapShotTooOld$$.txt"
}