Viewing Oracle sessions via a ksh script


This script is useful when you want to see current SQL sessions form the unix promp. The will show the first two lines of the SQL text, whether the session is ACTIVE or not, and the unix PID (in case you want to kill it), as well as the originating machine.


#!/bin/ksh
#
#
# Trailing queries can be used to query SQL for requested sessions.
#
# See also: sqltext.sql to query a sessions SQL statment.
# This will display UnixID, which is usefull, if you wish to kill the session
#
# Written by: Mike Rudnik
# Rudnik Consulting Inc
#
echo $$
sqlplus -s "/ as sysdba" <<'%'

set termout on
set echo on
set line 250
set pagesize 999
rem ACTIVE USERS
col ID for a10
col username for A10
col OSuser for A8
col machine for a10
col process for A7
col UnixID for A7
col SV for A1
col LK for A1
col "SESSION SQL" for A50

select decode(nvl(px.sid,0),0,'',
decode(sign(px.sid-px.qcsid),0,'','|'))||
S.SID||','||S.serial# as id,
S.username, S.OSuser,
S.status||decode(sign(last_call_et-30),-1,'*','') Status,
decode(nvl(px.sid,0),0,S.machine,
decode(sign(px.sid-px.qcsid),0,'['||S.machine||']',' '||
px.qcsid||' '||px.server_set||','||px.server#)) machine,
rpad(S.server,1) SV,
decode(S.lockwait,NULL,'N','Y') LK,
--initcap(A.Name) Command,
decode(S.process,
null,S.process,
decode(instr(S.process,':'),
0,S.process,
'windows')) process,
P.SPID "UnixID", substr(t.sql_text,1,50) as "SESSION SQL"
from v$SESSION S, v$PROCESS P, sys.AUDIT_ACTIONS A,
v$px_session px, (select a.address, replace(replace(replace(a.sql_text||b.sql_text,' ',' '),' ',' '),' ',' ') as sql_text from v$sqltext a, v$sqltext b where a.address=b.address and a.piece=0 and b.piece=1) t
where S.username is not null
and s.sql_address= t.address (+)
and A.action = S.command
and P.ADDR(+) = S.PADDR
and S.SADDR = px.SADDR(+)
and S.audsid != userenv('sessionid')
order by px.qcsid,
nvl(px.qcserial#,0),
nvl(px.server_set,0),
nvl(px.server#,0),S.SID
/
quit
/
select username,sid,serial#,sql_text
from v$session s,v$sqltext t
where s.sql_address = t.address
and s.sql_hash_value = t.hash_value
order by piece
/
prompt 'Print a sessions SQL'
select sql_text
from v$session s,v$sqltext t
where s.sql_address = t.address
and s.sql_hash_value = t.hash_value
and s.sid = &sid
and s.serial# = &serial
order by piece
/
select sql_text
from v$session s,v$sqltext t
where s.prev_sql_addr = t.address
and s.prev_hash_value = t.hash_value
and s.sid = &&sid
and s.serial# = &&serial
order by piece
/
quit
%




Copyright © 2007, All rights reserved by RudnikConsulting Inc