#!/usr/bin/ksh
# whatsql ()
#------------------------------------------------------------------------------#
#    Module Name:  whatsql                                                     #
#                                                                              #
#    Purpose:      Report on the Active SQL Sessions in Unix PID Order.        #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------- #
#    10-Oct-2009   T.Robinette   Module design/creation                        #
#                  F.Bender      Module design/creation                        #
#                  A.Estrada     Module design/creation                        #
#                  M.Barone      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  A6      Heading 'Unix|User'
        COLUMN  SESS_username   FORMAT  A6      Heading 'Oracle|User'
        COLUMN  PROC_spid       FORMAT  A5      Heading 'Unix|ID'
        COLUMN  SESS_sid        FORMAT  99999   Heading 'Oracle|Session'
        COLUMN  SESS_serial#    FORMAT  99999   Heading 'Session|Serial'
        COLUMN  SESS_status     FORMAT  A8      Heading 'Session|Status'
        COLUMN  SESS_terminal   FORMAT  A8      Heading 'User|Terminal'
        COLUMN  SQLA_text       FORMAT  A26     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  '[7m${ORANAM} (${HNAME}) Apps SQLText[0m'       -
                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,
                PROC.spid                       PROC_spid,
                SESS.sid                        SESS_sid,
                SESS.serial#                    SESS_serial#,
                SESS.status                     SESS_status,
		SESS.terminal			SESS_terminal,
                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
}
