Scripts - Locking

sql> select username,
v$lock.sid,
trunc(id1/power(2,16)) rbs,
bitand(id1,to_number('ffff','xxxx'))+0 slot,
id2 seq,
lmode,
request
from v$lock, v$session
where v$lock.type = 'TX'
and v$lock.sid = v$session.sid
and v$session.username = USER
/

USERNAME SID RBS SLOT SEQ LMODE REQUEST
-------- ---------- ---------- ---------- ---------- ---------- ----------
TKYTE 8 2 46 160 6 0



sql> select XIDUSN, XIDSLOT, XIDSQN
from v$transaction
/

XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
2 46 160


sql> select
(select username from v$session where sid=a.sid) blocker,
a.sid,
' is blocking ',
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2
/

BLOCKER SID 'ISBLOCKING' BLOCKEE SID
-------- ---------- ------------- -------- ----------
MANZ 8 is blocking AHAMED 9

sql> select
(select username from v$session where sid=a.sid) BLOCKER,
a.sid Blocker_SID,
(select serial# from V$session where sid=a.sid) SERIAL#,
' Is Blocking ',
(select username from V$session where sid=b.sid) Blocked_User,
b.sid Blocked_SID
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2
/

BLOCKER BLOCKER_SID SERIAL# 'ISBLOCKING' BLOCKED_USER BLOCKED_SID
---------- ----------- ---------- ------------- ------------ -----------
MANZOOR 158 12547 Is Blocking MANZOOR 138
MANZOOR 158 12547 Is Blocking MANZOOR 131
MANZOOR 158 12547 Is Blocking MANZOOR 148


sql> select BLOCKER, SID, SERIAL#,count(*) from
(
select
(select username from v$session where sid=a.sid) BLOCKER,
a.sid SID,
(select serial# from V$session where sid=a.sid) SERIAL#
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2
) group by blocker,sid,serial#
/

BLOCKER SID SERIAL# COUNT(*)
------------------------------ ---------- ---------- ----------
SYS 140 1327 2
MANZOOR 130 1748 2


Row Locks


DBA_BLOCKERS – Shows non-waiting sessions holding locks being waited-on.
DBA_DDL_LOCKS – Shows all DDL locks held or being requested.
DBA_DML_LOCKS - Shows all DML locks held or being requested.
DBA_LOCK_INTERNAL – Displays 1 row for every lock or latch held or being
requested with the username of who is holding the lock.
DBA_LOCKS - Shows all locks or latches held or being requested.
DBA_WAITERS - Shows all sessions waiting on, but not holding waited for locks.

Find the Session which is blocking others:-

SQL> select 'Session ' || session_id || ' Is blocking other session' "Message" from dba_locks where blocking_others <> 'Not Blocking';

Message
--------------------------------------
Session 141 Is blocking other session


Find the count of locked session by an Session:

SQL>select distinct(sid), count(*) from (select s1.sid from v$lock l1,
v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid
and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and
l1.id1 = l2.id1 and l2.id2 = l2.id2) group by sid order by count(*)
/

SID COUNT(*)
---------- ----------
141 2


Find for how long the blocked session are waiting

SQL> select a.sid, a.event, a.total_waits, a.time_waited, a.average_wait
from v$session_event a, v$session b
where time_waited > 0
and a.sid=b.sid
and b.username is not NULL
and a.event='enq: TX - row lock contention';

SID EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
---------- ------------------------------- ----------- ----------- ------------
153 enq: TX - row lock contention 193 57894 299.97
158 enq: TX - row lock contention 46 13798 299.96



Find the all the locked objects:-

set pages 100 lines 200
col object_name for a25
col machine for a20
col oracle_username for a15
col lock_mode for a20
break on session_id on os_user_name skip 1 on oracle_username on clt_pid on svr_pid on machine

col OS_USER_NAME form a12;
col ORACLE_USERNAME form a7;
col ORACLE_USERNAME form a12;
col MACHINE form a25;
col OBJECT_NAME form a25;

SELECT session_id, os_user_name,oracle_username, v$locked_object.process clt_pid,spid svr_pid, machine||':'||substr(v$session.program,1,10) machine,
object_name,decode(locked_mode, 0, 'None',1, 'Null',2, 'Row-S (SS)', 3, 'Row-X (SX)', 4,'Share', 5, 'S/Row-X (SSX)',6, 'Exclusive',to_char(locked_mode)) lock_mode from dba_objects,v$process p,v$locked_object, v$session where v$locked_object.object_id = dba_objects.object_id
and session_id = sid and paddr = addr order by session_id,v$locked_object.process, session_id, machine
/



script to display selected sessions and related process information
for all Oracle sessions blocked from processing due to a lock held
by another session.
====================


set verify off
column machine format a08 heading "APPL.|MACHINE"
column sid format 99999
column serial# format 99999
column spid format 99999 heading "ORACLE|SERVER|PROCESS"
column process format 99999 heading "USER|APPL.|PROCESS"
column username format a12


select
s.username, s.status, s.sid, s.serial#,
p.spid, s.machine, s.process, s.lockwait
from v$session s, v$process p
where s.lockwait is not null
and s.paddr = p.addr
/


script to display Oracle dml locks held by an Oracle session.
=============================================================


set verify off
set feedback on
column "Mode Held" format a9
column "Mode Req " format a9

REM
REM If type = TM then id1 = object id
REM TX rbs number and slot
REM id2 = wrap number
REM

select
sid, type, id1, id2,
decode(lmode,
0,'WAITING' ,
1,'Null' ,
2,'Row Shr' ,
3,'Row Exc' ,
4,'Share' ,
5,'Shr Row X',
to_char(lmode)
) "Mode Held",
decode(request,
0,'None' ,
1,'Null' ,
2,'Row Shr' ,
3,'Row Exc' ,
4,'Share' ,
5,'Shr Row X',
6,'Exclusive',
to_char(request)
) "Mode Req "
from v$lock
where sid = &session
/


script to display all Oracle sessions holding or attempting to obtain a particular lock (id1) on an object.
==================================================================

set verify off
set feedback on
column "Mode Held" format a9
column "Mode Req " format a9

rem
rem If type = TM then id1 = object id
rem TX rbs number and slot
rem id2 = wrap number
rem

select
sid, type, id1, id2,
decode(lmode,
0,'WAITING' ,
1,'Null' ,
2,'Row Shr' ,
3,'Row Exc' ,
4,'Share' ,
5,'Shr Row X',
6,'Exclusive',
to_char(lmode)
) "Mode Held",
decode(request,
0,'None' ,
1,'Null' ,
2,'Row Shr' ,
3,'Row Exc' ,
4,'Share' ,
5,'Shr Row X',
6,'Exclusive',
to_char(request)
) "Mode Req "
from v$lock
where id1 = '&lockno'
/
---------------------------------------------------------------------
When a session is waiting for lock, how to find the blocking session and waiting for object and row ...

To find who is blocking ?

select sb.username || '@' || sb.machine
|| ' ( SID=' || sb.sid || ' ) is blocking '
|| sw.username || '@' || sw.machine || ' ( SID=' || sw.sid || ' ) ' AS blocking_status
from v$lock lb, v$session sb, v$lock lw, v$session sw
where sb.sid=lb.sid and sw.sid=lw.sid
and lb.BLOCK=1 and lw.request > 0
and lb.id1 = lw.id1
and lw.id2 = lw.id2 ;

Waiting for object and rowid ...

select o.object_name, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, o.DATA_OBJECT_ID, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects o where sid=&waiting_sid and s.ROW_WAIT_OBJ# = o.OBJECT_ID ;

Waiting for row..

select * from table_name_from_above where rowid =&rowid_returned

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

Oracle library cache pin waits are caused by contention with the library cache, the area used to store SQL executables for re-use. The library cache pin Oracle metric takes place if the process wants to pin an object in memory in the library cache for examination, ensuring no other processes can update the object at the same time. The library cache pin wait usually happens when you are compiling or parsing a PL/SQL object or a view.

The following query provides clues about whether Oracle has been waiting for library cache activities:

select
sid,
event,
p1raw,
seconds_in_wait,
wait_time
from
v$session_wait
where
event = 'library cache pin'
and
state = 'WAITING';

This is my script for tracking down who is blocking who in the event of a library cache pin event:

select decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, 'MATERIALIZED VIEW',
43, 'DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'SECURITY PROFILE', 59, 'RULE',
62, 'EVALUATION CONTEXT',
'UNDEFINED') object_type,
lob.KGLNAOBJ object_name,
pn.KGLPNMOD lock_mode_held,
pn.KGLPNREQ lock_mode_requested,
ses.sid,
ses.serial#,
ses.username
FROM
x$kglpn pn,
v$session ses,
x$kglob lob,
v$session_wait vsw
WHERE
pn.KGLPNUSE = ses.saddr and
pn.KGLPNHDL = lob.KGLHDADR
and lob.kglhdadr = vsw.p1raw
and vsw.event = 'library cache pin'
order by lock_mode_held desc
/