Match OS process to Oracle session
Your server is under pressure and you have decided to kill the oracle session which is causing excessive
CPU or memory consumption, here’s how you can find the oracle session associated with a unix process:
Use top on the unix command line to see which process is the main culprit:
(use shift+M to sort the list in order of highest memory consumption)
top – 15:35:38 up 81 days, 47 min, 2 users, load average: 0.03, 0.04, 0.06
Tasks: 148 total, 2 running, 146 sleeping, 0 stopped, 0 zombie
Cpu(s): 1.5% us, 0.0% sy, 0.0% ni, 98.5% id, 0.0% wa, 0.0% hi, 0.0% si
Mem: 993492k total, 979396k used, 14096k free, 8568k buffers
Swap: 1998840k total, 392928k used, 1605912k free, 791300k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
4132 oracle 15 0 754m 149m 143m S 0.0 15.4 0:03.92 oracle
9581 oracle 15 0 754m 137m 132m S 0.0 14.2 0:01.70 oracle
5230 oracle 15 0 755m 132m 130m S 0.0 13.7 0:16.90 oracle
6404 oracle 16 0 754m 116m 111m S 0.0 12.0 0:02.75 oracle
5224 oracle 16 0 766m 99m 98m S 0.0 10.2 0:08.76 oracle
5234 oracle 16 0 754m 89m 87m S 0.0 9.2 0:06.84 oracle
Make sure the top process is a client connection shadow process:
oracle 4132 1 0 08:52 ? 00:00:03 oracletestdb (LOCAL=NO)
oracle 23557 23472 0 15:35 pts/1 00:00:00 grep 4132
Inside the database, find out who this is :
(formatted result)
SQL>
select
p.username,
s.sid,
s.serial#,
p.spid,
s.osuser,
s.status,
p.program,
p.terminal,
to_char(s.logon_time,’dd-mon-yyyy hh24:mi:ss’) “Logon Time”,
s.module
from
v$process p,
v$session s
where
p.addr = s.paddr
and p.spid = ’4132′;
USERNAME SID SERIAL# SPID OSUSER STATUS
———— —- ——- —– —— ——-
devuser 393 1178 4132 LarryE ACTIVE
PROGRAM TERMINAL Logon Time MODULE
—————————— ————- ———————- —————
oracletestdb@test.world.com UNKNOWN 16-feb-2008 13:18:20 TOAD 9.0.1.8
Kill the user:
System altered.
The user will receive this message either immediately or at the next attempted SQL statement:
Recommended reading:
Posted: October 23rd, 2009 under Oracle Scripts.
Tags: logon time, memory consumption, oracle os session id, paddr, shr, spid, time command, unix command line, virt, yyyy, zombie