#!/usr/bin/ksh
# whatawr ()
#------------------------------------------------------------------------------#
#    Module Name:  whatawr   (c) May 2012                                      #
#                                                                              #
#    Purpose:      Report on the v$SQL Sessions.                               #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    10-May-2012   M.Barone      Module design/creation                        #
#------------------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)
                
        clear
        #----------------------------------------------------------------------#
        #  TEST:  Number ($#) of arguments passed to this function?            #
        #----------------------------------------------------------------------#
        if      [ $# = 0 ]
        then
            clear
            echo
            echo  " #--------------------------------------------------------#"
            echo  " # whatawr:          Oracle Automatic Workload Repository #"
            echo  " #--------------------------------------------------------#"
            echo

            print
            echo  " Connecting as SYSDBA"
            sleep 2

            clear
        else
            SEARCH=$1
            clear
        fi

        ORANAM=${ORASID}

#------------------------------------------------------------------------------#
#  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 /tmp/whatawr$$.txt

        TTITLE  CENTER  '${ORANAM} (${HNAME}) AWR Controls'  skip 2

        COLUMN  DBID                    FORMAT 9999999999 Heading 'DB|ID'
        COLUMN  SNAP_INTERVAL           FORMAT A20      Heading 'Snap|Interval'
        COLUMN  RETENTION               FORMAT A20      Heading 'Retention'
        COLUMN  TOPNSQL                 FORMAT A20      Heading 'TopNSQL'

        SELECT  DBID, SNAP_INTERVAL, RETENTION,
                DECODE(TOPNSQL, 'DEFAULT', 'Detfault=10', TOPNSQL) TOPNSQL
        FROM    DBA_HIST_WR_CONTROL;


        TTITLE  CENTER  '${ORANAM} (${HNAME}) AWR Details'  skip 2

        COLUMN  SNAP_ID                 FORMAT 99999    Heading 'Snap|ID'
        COLUMN  BEGIN_INTERVAL_TIME     FORMAT A25      Heading 'Starting|Time'
        COLUMN  END_INTERVAL_TIME       FORMAT A25      Heading 'Stoping|Time'

        SELECT  SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME
        FROM    DBA_HIST_SNAPSHOT
        ORDER BY        SNAP_ID;

EXIT;

END_FILE
print
print "Please see /tmp/whatawr$$.txt"
print "       ORACLE_HOME/rdbms/admin/awrrpt.sql  (AWR Report)"
print "       ORACLE_HOME/rdbms/admin/ashrpt.sql  (Active Session History)"
print "       ORACLE_HOME/rdbms/admin/addmrpt.sql (Auto DB-Diagnostic Monitor)"
print
}
