sessions script 2

See any locking session

select * from dba_locks
order by blocking_others

Use utllockt.sql to see locking sessions

Remember that catblock.sql should have ran, you can verify this by using:
select * from dba_locks

sqlplus (windows)>@?\rdbms\admin\utllockt

sqlplus (unix)>@?/rdbms/admin/utllockt

WAITING_SESSION LOCK_TYPE MODE_REQUESTED MODE_HELD LOCK_ID1 LOCK_ID2
----------------- ----------------- -------------- -------------- ----------------- -----------------
20 None
9 DML Exclusive Exclusive 54132 0

In this case the session 20 is locking session 9, so kill session 20

Library cache pin locks

SELECT sid, event, p1raw, seconds_in_wait, wait_time
FROM sys.v_$session_wait
WHERE event = 'library cache pin'

SELECT 'alter system kill session ''' || s.sid || ',' || s.serial# || ''';'
, 'kill -9 ' || spid
FROM x$kglpn p, v$session s, v$process
WHERE p.kglpnuse=s.saddr
AND kglpnhdl='DA741F24'
and v$process.addr = s.paddr

A session is hang, kill anyone interfering

SELECT *
FROM v$access
WHERE OBJECT IN (SELECT object_name
FROM dba_objects
WHERE object_id IN (SELECT object_id
FROM v$locked_object
WHERE session_id = 32))

select 'ALTER SYSTEM DISCONNECT SESSION ''' || sid || ', ' || v$session.serial# ||
''' immediate/*spid=' || spid || '*/'
from v$session, v$process
where sid in (
select sid from v$access
where object = 'VTCONNECTORSTATE'
group by sid
)
and v$process.addr = v$session.paddr

select * from (
SELECT s.schemaname, p.username, s.sid, p.pid, p.spid, s.username ora,
DECODE(l2.type, 'TX','TRANSACTION ROW-LEVEL', 'TS','TEMPORARY SEGMENT ', 'TD','TABLE LOCK',
'TM','ROW LOCK', l2.type ) vlock,
DECODE(l2.type, 'TX','DML LOCK', 'TS','TEMPORARY SEGMENT', 'TD',DECODE(l2.lmode+l2.request,
4,'PARSE ' || u.name || '.' || o.name, 6,'DDL', l2.lmode+l2.request), 'TM','DML ' || u.name ||
'.' || o.name, l2.type ) type,
DECODE(l2.lmode+l2.request, 2,'RS', 3 ,'RX', 4 ,'S', 5 ,'SRX', 6 ,'X', l2.lmode+l2.request ) lmode ,
DECODE(l2.request, 0,NULL, 'WAIT') wait
FROM v$process p, v$_lock l1, v$lock l2, v$resource r, sys.obj$ o, sys.user$ u, v$session s
WHERE s.paddr = p.addr
AND s.saddr = l1.saddr
AND l1.raddr = r.addr
AND l2.addr = l1.laddr
AND l2.type <> 'MR'
AND l2.type <> 'RT'
AND r.id1 = o.obj# (+)
AND o.owner# = u.user# (+)
)

Lock simulation

The updates performed in this example does not modify the actual values!

select id, de_VC1 from prova where id in (193556, 196756)
ID DE_VC1
---------- --------------------
193556 mon
196756 mon

session 1, sid 145>update prova set de_VC1 = 'sess1' where id = 193556;
1 row updated.

SELECT v$session.username, dba_locks.*
FROM dba_locks, v$session
WHERE v$session.SID = dba_locks.session_id AND session_id = 145

username session_id lock_type mode_held mode_requested lock_id1 lock_id2 last_convert blocking_others
ALDO 145 DML Row-X (SX) None 53176 0 270 Not Blocking
ALDO 145 Transaction Exclusive None 196615 676 270 Not Blocking

session 2, sid 156>update prova set de_VC1 = 'sess2' where id = 196756;
1 row updated.

SELECT v$session.username, dba_locks.*
FROM dba_locks, v$session
WHERE v$session.SID = dba_locks.session_id AND session_id = 156

username session_id lock_type mode_held mode_requested lock_id1 lock_id2 last_convert blocking_others
ALDO 156 DML Row-X (SX) None 53176 0 270 Not Blocking
ALDO 156 Transaction Exclusive None 262191 680 270 Not Blocking

session 1, sid 145>update prova set de_VC1 = 'sess1' where id = 196756;
--session hang waiting
SELECT v$session.username, dba_locks.*
FROM dba_locks, v$session
WHERE v$session.SID = dba_locks.session_id AND session_id IN (145, 156)
ORDER BY session_id

username session_id lock_type mode_held mode_requested lock_id1 lock_id2 last_convert blocking_others
ALDO 145 Transaction Exclusive None 196615 676 883 Not Blocking
ALDO 145 DML Row-X (SX) None 53176 0 883 Not Blocking
ALDO 145 Transaction None Exclusive 262191 680 81 Not Blocking
ALDO 156 Transaction Exclusive None 262191 680 349 Blocking
ALDO 156 DML Row-X (SX) None 53176 0 349 Not Blocking

session 2, sid 156>update prova set de_VC1 = 'sess2' where id = 193556;
--session hang waiting
session 1, sid 145>ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
session 2, sid 156>--continue waiting

SELECT v$session.username, dba_locks.*
FROM dba_locks, v$session
WHERE v$session.SID = dba_locks.session_id AND session_id IN (145, 156)
ORDER BY session_id

username session_id lock_type mode_held mode_requested lock_id1 lock_id2 last_convert blocking_others
ALDO 145 Transaction Exclusive None 196615 676 1246 Blocking
ALDO 145 DML Row-X (SX) None 53176 0 1246 Not Blocking
ALDO 156 Transaction Exclusive None 262191 680 712 Not Blocking
ALDO 156 Transaction None Exclusive 196615 676 9 Not Blocking
ALDO 156 DML Row-X (SX) None 53176 0 712 Not Blocking



Trace generated

Dump file c:\oracle\product\10.2.0\admin\al12\udump\al12_ora_5428.trc
Wed Feb 22 11:27:34 2006
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 2
CPU : 1 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:985M/2046M, Ph+PgF:2957M/3938M, VA:1713M/2047M
Instance name: al12

Redo thread mounted by this instance: 1

Oracle process number: 19

Windows thread id: 5428, image: ORACLE.EXE (SHAD)


*** 2006-02-22 11:27:34.171
*** ACTION NAME:() 2006-02-22 11:27:34.156
*** MODULE NAME:(SQL*Plus) 2006-02-22 11:27:34.156
*** SERVICE NAME:(al12) 2006-02-22 11:27:34.156
*** SESSION ID:(35.38) 2006-02-22 11:27:34.156
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
update prova set de_VC1 = 'sess1' where id = 196756 (the session was waiting, then take ORA-00060)
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00020021-000005aa 19 35 X 20 46 X
TX-0003001f-000005b4 20 46 X 19 35 X
session 35: DID 0001-0013-00000007 session 46: DID 0001-0014-00000005
session 46: DID 0001-0014-00000005 session 35: DID 0001-0013-00000007
Rows waited on:
Session 46: obj - rowid = 0000CFB8 - AAAM+4AAHAAABAaAAB
(dictionary objn - 53176, file - 7, block - 4122, slot - 1)
Session 35: obj - rowid = 0000CFB8 - AAAM+4AAHAAABAaAAE
(dictionary objn - 53176, file - 7, block - 4122, slot - 4)
Information on the OTHER waiting sessions:
Session 46:
pid=20 serial=30 audsid=4264 user: 55/ALDO
O/S info: user: root, term: D2E7B5729B, ospid: 5276:1080, machine: WORKGROUP\D2E7B5729B
program: sqlplus.exe
application name: SQL*Plus, hash value=3669949024
Current SQL Statement:
update prova set de_VC1 = 'sess2' where id = 193556 (the session may continue waiting if sess1 do nothing)
End of information on OTHER waiting sessions.
===================================================

Get all long running queries
SELECT /*+ rule */
SYSDATE tracciamento, a.SID SID, b.serial# serial, b.last_call_et,
a.event wait_event, b.username username, b.machine machine,
b.program programma, sq.sql_text
FROM v$session_wait a, v$session b, v$sql sq
WHERE event NOT IN
('smon timer',
'pmon timer',
'rdbms ipc message',
'SQL*Net message from client',
'SQL*Net message to client',
'SQL*Net more data to client',
'SQL*Net message from dblink',
'jobq slave wait'
)
AND a.SID = b.SID
AND b.sql_address = sq.address
AND b.last_call_et > 60
AND b.username NOT IN ('SYS', 'SYSTEM', 'EXPDB')
AND b.SID NOT IN (SELECT SID
FROM dba_jobs_running)
ORDER BY b.last_call_et DESC


Master sessions and parallel spawned sessions

col username for a12
col "QC SID" for A6
col SID for A6
col "QC/Slave" for A10
col "Requested DOP" for 9999
col "Actual DOP" for 9999
col "slave set" for A10
set pages 100

select
decode(px.qcinst_id,NULL,username,
' - '||lower(substr(s.program,length(s.program)-4,4) ) ) "Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "Slave Set",
to_char(s.sid) "SID",
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
px.req_degree "Requested DOP",
px.degree "Actual DOP"
from
v$px_session px,
v$session s
where
px.sid=s.sid (+)
and
px.serial#=s.serial#
order by 5 , 1 desc;