Locate and terminate a locked session in Oracle9i

Because Oracle has an internal locking mechanism, it is sometimes useful to find and terminate a session in Oracle that locks a database resource. First, you need to find all the Oracle sessions. Here is a script that shows the details of all the sessions within Oracle.

Remsession.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 "dbnamedatabase| 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 Page 11
DBName Database
Unix/oracle Sessions

PID SID ser# BOX USERNAME os_user Program
--------- ----- ----- ------ ---------- -------- ------------------------------
6230 Wuyi 251 Mwc\co APPS teilers S:\ORANT\BIN\F50RUN32. Exe
6233 2729 Mwc\co APPS teilers S:\ORANT\BIN\R30RBE32.exe
6823 661 Corp-h APPS applmgrF45RUNM@CORP-HP1(TNS v1-v3)
6823 317 corp-h APPS applmgr
779 122 1307 Corp-h APPS applmgr
9322 116 242 45a_10 APPS Lmichel. Exe
9330 440 corp-h APPS applmgr

Once we've found all the sessions in Oracle, the next step is to run a script that detects all the sessions that have been locked. This is because Oracle may not be able to detect an inactive (dead) session fast enough to prevent blocking of data access (blockage). You can run the following script to locate the session that controls the locking of the resource.

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
----- ----- ---------- ---------- ------------------------------ -----------
APPS Applmgr fnd_concurrent_requests 2
535 APPS applmgr Mtl_system_items 2
126 161 APPS Oracle So_lines_all 2

Once a locked session is positioned, you can use the following script to automatically create ALTER session syntax to terminate the conversation you wish to terminate, so that locked sessions can be purged from Oracle.

Spoolrun_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 create the Run_nuke.sql file, you can quickly select the sessions to be terminated and run them individually.