 |
 |
 |
 |
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
|
|
 |
|
|
|
 |
|
 |
Donald K. Burleson Oracle Tips |
Finding and killing locked sessions in Oracle9i
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
|
|
