#!/usr/bin/ksh
# whatsql ()
#------------------------------------------------------------------------------#
# Module Name: whatsqlhash #
# #
# 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 " # whatsqlhash: 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 9999

 SPOOL /tmp/whatsql_$$.txt

 COLUMN datevalue NOPRINT NEW_VALUE DATEVAR
 COLUMN timevalue NOPRINT NEW_VALUE TIMEVAR

 COLUMN SESS_sid FORMAT 99999 Heading 'Oracle|Session'
 COLUMN SESS_serial# FORMAT 99999 Heading 'Session|Serial'
 COLUMN SQLA_hash FORMAT 99999999999999 Heading 'SQL Hash Value'
 COLUMN SQLA_sqlid FORMAT A14 Heading 'SQL-ID'
 COLUMN SQLA_text FORMAT A28 Heading 'SQL Text' -
 WORD_WRAPPED

 BREAK ON PROC_spid skip 1 ON SESS_sid skip 0 -
 ON SQLA_hash skip 0 ON SQLA_sqlid skip 0

 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.spid PROC_spid,
 SESS.sid SESS_sid,
 SQLA.hash_value SQLA_hash,
 SQLA.sql_id SQLA_sqlid,
 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/whatsqlhash_$$.txt"
print
}
