Finding and killing locked
sessions in Oracle
Because of Oracles internal locking, there
are times when it is useful to be able to
locate and kill Oracle sessions that are
locking database resources. For
complete details and scripts, see
my book "Oracle
Tuning: The Definitive Reference".
First, here is a script to display details
about all sessions within Oracle.
--**************************************************************
-- session.sql © 2002 by Donald K. Burleson
--**************************************************************
rem session.sql - displays all connected
sessions
set echo off;
set termout on;
set linesize 80;
set pagesize 60;
set newpage 0;
select
rpad(c.name||':',11)||rpad(' current
logons='||
(to_number(b.sessions_current)),20)||'cumulative
logons='||
rpad(substr(a.value,1,10),10)||'highwater
mark='||
b.sessions_highwater Information
from
v$sysstat a,
v$license b,
v$database c
where
a.name = 'logons cumulative'
;
ttitle "dbname Database|UNIX/Oracle
Sessions";
set heading off;
select 'Sessions on database
'||substr(name,1,8) from v$database;
set heading on;
select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program
from
v$session b,
v$process a
where
b.paddr = a.addr
and
type='USER'
order by
spid;
ttitle off;
set heading off;
select 'To kill, enter SQLPLUS> ALTER SYSTEM
KILL SESSION',
''''||'SID, SER#'||''''||';' from dual;
spool off;
Tue Mar 19 page 11
dbname Database
UNIX/Oracle Sessions
PID SID SER# BOX USERNAME OS_USER PROGRAM
--------- ----- ----- ------ ----------
-------- -------------------------
6230 51 251 MWC\CO APPS TEilers S:\ORANT\BIN\F50RUN32.EXE
6233 69 2729 MWC\CO APPS TEilers S:\ORANT\BIN\R30RBE32.exe
6823 75 661 corp-h APPS applmgr
f45runm@corp-hp1 (TNS V1-V3)
6823 85 317 corp-h APPS applmgr
779 122 1307 corp-h APPS applmgr
9322 116 242 45A_10 APPS lmichel
F50RUN32.EXE
9330 67 440 corp-h APPS applmgr
Once we see all sessions within Oracle, the
next step is to run a script to detect all
locked sessions. This is because Oracle may
not detect a dead session quickly enough to
prevent a blockage in data access. The first
script below can be run to locate those
sessions that are holding locked resources.
Once located, you can run this next script
to automatically create the "alter session"
syntax to kill the session that your desire
to remove the locked sessions from Oracle:
spool
run_nuke.sql
select
'alter system kill session '''||
sess.sid||', '||sess.serial#||';'
from
v$locked_object lo,
dba_objects ao,
v$session sess
where
ao.object_id = lo.object_id
and
lo.session_id = sess.sid;
After you have created the run_nuke.sql
file, you can quickly select those sessions
to kill and run them independently.
Changes to
v$sessions
In the past, sessions experiencing waits
were generally located by joining the
v$session_wait view with the v$sessions
view. To simplify the query, all the wait
event columns from v$session_wait have been
added to v$sessions.
Use the statement below to determine the
wait events that involve the most sessions.
SELECT
wait_class, count(username)
FROM v$session GROUP BY wait_class;
New columns have been added to v$sessions as
follows:
SQL_CHILD_NUMBER, PREV_CHILD_NUMBER,
BLOCKING_SESSION_STATUS, BLOCKING_SESSION,
SEQ#, EVENT#, EVENT, WAIT_CLASS#, WAIT_CLASS,
WAIT_TIME, SECONDS_IN_WAIT, STATE and
SERVICE_NAME
Changes to v$session_wait
The new columns include wait_class# and
wait_class.
|
and include the URL for the page.
