Donald K. Burleson Oracle Tips

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.

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.

select

   sess.sid,

   sess.serial#,

   lo.oracle_username,

   lo.os_user_name,
   ao.object_name,
   lo.locked_mode
from

   v$locked_object lo,

   dba_objects     ao,

   v$session       sess

where

   ao.object_id = lo.object_id

and

   lo.session_id = s.sid;

Tue Mar 19                                                             page    1

                                     Locked

                                    objects

            Oracle     OS         Object

  sid  ser# user       User       Name                           LOCKED_MODE

----- ----- ---------- ---------- ------------------------------ -----------

   21    56 APPS       applmgr    FND_CONCURRENT_REQUESTS                  2

   77   535 APPS       applmgr    MTL_SYSTEM_ITEMS                         2

  126   161 APPS       oracle     SO_LINES_ALL                             2

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 = s.sid;

After you have created the run_nuke.sql file, you can quickly select those sessions to kill and run them independently.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2003_1_oracle9i_sga.htm

Copyright © 1996 -  2009 by Burleson Enterprises, Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.