View blocking sessions

V$LOCK details


ADDR RAW(4)

Address of lock state object

KADDR RAW(4)

Address of lock

SID NUMBER 

Identifier for session holding or acquiring the lock

TYPE VARCHAR2(2) 

Type of user or system lock

The locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks. The user type locks are:

TM - DML enqueue

TX - Transaction enqueue

UL - User supplied

The locks on the system types are held for extremely short periods of time.

ID1 NUMBER

Lock identifier #1 (depends on type)

ID2 NUMBER 

Lock identifier #2 (depends on type)

LMODE NUMBER 

Lock mode in which the session holds the lock:

0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)

REQUEST NUMBER 

Lock mode in which the process requests the lock:

0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)

CTIME NUMBER

Time since current mode was granted

BLOCK   NUMBER

The lock is blocking another lock


View blocking sessions (BLOCK=1)

select

   blocking_session,
   sid,
   serial#,
   wait_class,

   seconds_in_wait
from
   v$session

where
   blocking_session is not NULL

order by
   blocking_session;

BLOCKING_SESSION        SID    SERIAL#

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

WAIT_CLASS                                                       SECONDS_IN_WAIT

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

              25         13       1357

Application                                                                  106

SYSTEM LOCK TYPES


BL
Buffer hash table instance

NA..NZ
Library cache pin instance (A..Z = namespace)

CF
Control file schema global enqueue

PF
Password File

CI
Cross-instance function invocation instance

PI, PS
Parallel operation

CU
Cursor bind

PR
Process startup

DF
Data file instance

QA..QZ
Row cache instance (A..Z = cache)

DL
Direct loader parallel index create

RT
Redo thread global enqueue

DM
Mount/startup db primary/secondary instance

SC
System commit number instance

DR
Distributed recovery process

SM
SMON

DX
Distributed transaction entry

SN
Sequence number instance

FS
File set

SQ
Sequence number enqueue

HW
Space management operations on a specific segment

SS
Sort segment

IN
Instance number

ST
Space transaction enqueue

IR
Instance recovery serialization global enqueue

SV
Sequence number value

IS
Instance state

TA
Generic enqueue

IV
Library cache invalidation instance

TS
Temporary segment enqueue (ID2=0)

JQ
Job queue

TS
New block allocation enqueue (ID2=1)

KK
Thread kick

TT
Temporary table enqueue

LA .. LP
Library cache lock instance lock (A..P = namespace)

UN
User name

MM
Mount definition global enqueue

US
Undo segment DDL

MR
Media recovery

WL
Being-written redo log instance



Meaning of ID1 and ID2 depend on the lock TYPE.

   
      TYPE  Name ID1                     ID2
     
      TX    Transaction Decimal RBS & slot Decimal WRAP number
                    (0xRRRRSSSS  RRRR = RBS number, SSSS = slot )
    A TX lock is requested in eXclusive mode if we are waiting on a ROW.
A SHARE mode request implies we are waiting some other resource held
    by the TX
   
      TM Table Locks Object id of table. Always 0.
   
      TS Temp Segment TS# Relative DBA
   
      ST Space Transaction Only ONE enqueue.
      UL   User Locks

Where RBS - rollback segment