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