User session Monitor from Shell Script
[oracle@superlinux ~]$ cat user_session_monitor.sh#!/bin/sh
if [ $# -ne 1 ]
then echo "Usage: $0 pass the pid"
exit 1
fi
HOSTNAME=`hostname`
U=$1
export U
sqlplus -s "/ as sysdba" << EOI
set linesize 500
col hostname for a15
col program for a15
col username for a15
col osuser for a15
col sid for a10
col spid for a10
select s.sid "SID",p.spid "PID",s.username "USERNAME",s.osuser "OS-USER",s.machine "HOSTNAME",s.program "PROGRAM",s.status "STATUS" from v\$process p, v\$session s where upper(p.spid)=upper('$U') and p.addr = s.paddr;
exit;
EOI
sqlplus -s "/ as sysdba" <<EOI
set head off
set long 400000
! echo "The User Query Is ...."
select sql_text from v\$sqltext where address = (select sql_address from v\$session where paddr = (select addr from v\$process where spid = $U)) order by piece;
exit;
EOI
Usage of the shell script file
find out the process which is consuming lot of resource using top command and use the script as below
[oracle@superlinux ~]$ sh user_session_monitor.sh
Usage: user.sh pass the pid
[oracle@superlinux ~]$ sh user_session_monitor.sh 20053(OS pid) SID PID USERNAME OS-USER HOSTNAME PROGRAM STATUS ---------- ------------ --------------- ------------------------------ --------------- --------------- -------- ########## 20053 ORION mfarhan NATJED\ABDULWAH INACTIVE AB The User Query Is .... SELECT IEL_ID, IEL_TYPE, HIL_HN_ID, HIL_EXP_ID, NOTM FROM ORION .EUL_IG_EXP_LINKS WHERE IEL_TYPE='HIL' AND HIL_EXP_ID=:HIL_EXP _ID
