#!/bin/ksh
# whatsql ()
#------------------------------------------------------------------------------#
#    Module Name:  whatsql                                                     #
#                                                                              #
#    Purpose:      Report on the Active SQL Sessions in Unix PID Order.        #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------- #
#    10-Oct-2009   F.Bender      Module design/creation                        #
#------------------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)
 
        clear
        echo
        echo  " #--------------------------------------------------------#"
        echo  " # whatsql:                     Oracle Apps SQLText Area  #"
        echo  " #--------------------------------------------------------#"
        echo
        echo  " Connecting as SYSDBA"
        sleep 2
 
        clear
        ORANAM=${ORASID}
 
        ORD_LNE="PROC.username, SESS.username, PROC.spid, SESS.sid"
        ORD_LNE="${ORD_LNE}, SESS.serial#, SESS.status, SQLA.piece"
 
        SQL_LNE="ORDER BY ${ORD_LNE}"
 
#------------------------------------------------------------------------------#
#  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/whatsql_$$.txt
 
        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR
 
        COLUMN  PROC_username   FORMAT  A7        Heading 'Unix|User'
        COLUMN  SESS_username   FORMAT  A6        Heading 'Oracle|User'
        COLUMN  SESS_audsid     FORMAT  999999999 Heading 'Session|AUDSID'
        COLUMN  PROC_spid       FORMAT  A5        Heading 'Unix|ID'
        COLUMN  SESS_sid        FORMAT  99999     Heading 'Oracle|Sessn'
        COLUMN  SESS_serial#    FORMAT  99999     Heading 'Oracle|Serial'
        COLUMN  SESS_status     FORMAT  A8        Heading 'Session|Status'
        COLUMN  SQLA_text       FORMAT  A25       Heading 'SQL Text'      -
                                                  WORD_WRAPPED
 
        BREAK   ON PROC_username skip 1 ON SESS_username skip 0         -
                ON SESS_status   skip 0 ON SESS_sid      skip 0         -
                ON SESS_serial#  skip 0 ON PROC_spid     skip 1
 
        TTITLE  CENTER  '[{ORANAM} (${HNAME}) Apps SQLText]'                -
                LEFT    DATEVAR                                     skip 1  -
                LEFT    TIMEVAR                                     skip 2
 
       SELECT  TO_CHAR(sysdate, 'DD-MON-YY')           datevalue,
               TO_CHAR(sysdate, 'HH24:MI:SS')          timevalue,
                PROC.username                   PROC_username,
                SESS.username                   SESS_username,
                SESS.audsid                     SESS_audsid,
                PROC.spid                       PROC_spid,
                SESS.sid                        SESS_sid,
                SESS.serial#                    SESS_serial#,
                SESS.status                     SESS_status,
                SUBSTR(SQLA.sql_text, 1, 4000)  SQLA_text
          FROM  sys.v\$process  PROC,
                sys.v\$session  SESS,
                sys.v\$sqltext  SQLA
         WHERE  PROC.addr               = SESS.paddr
           AND  SESS.username   IS NOT    NULL
           AND  SESS.sql_address        = SQLA.address(+)
           AND  SESS.sql_hash_value     = SQLA.hash_value(+)
         ${SQL_LNE};
EXIT;
 
END_FILE
print
print "Please see /tmp/whatsql_$$.txt"
print
}