oracle查看session阻塞,oracle session阻塞查询

SQL> select sid, type, id1, id2, lmode, request, block from v$lock  where  id1=51148;

SID TY        ID1        ID2      LMODE    REQUEST      BLOCK

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

1644 TM      51148          0          6          0          1

1632 TM      51148          0          0          6          0

SQL>

SQL>

SQL>

SQL> select  ( select username from v$session where sid=a.sid) blocker , a.sid, (select username from v$session where sid=b.sid) blockee, b.sid from v$lock a , v$lock b where a.id1=b.id1 and a.id2 = b.id2 and a.block=1 and b.request > 0;

BLOCKER                               SID BLOCKEE                               SID

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

SCOTT                                1644 SCOTT                                1632

注：sid = 1644 的session 阻塞了sid=1632的 session.

V$LOCK

This view lists the locks currently held by the Oracle Database and outstanding requests for a lock or latch.

ColumnDatatypeDescriptionADDRRAW(4 | 8)Address of lock state object

KADDRRAW(4 | 8)Address of lock

SIDNUMBERIdentifier for session holding or acquiring the lock

TYPEVARCHAR2(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. The system type locks are listed in .

ID1NUMBERLock identifier #1 (depends on type)

ID2NUMBERLock identifier #2 (depends on type)

LMODENUMBERLock 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)

REQUESTNUMBERLock 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)

CTIMENUMBERTime since current mode was granted

BLOCKNUMBERA value of either 0 or 1, depending on whether or not the lock in question is the blocker.

Table 6-1 Values for the TYPE Column: System TypesSystem TypeDescriptionSystem TypeDescriptionBLBuffer hash table instanceNA..NZLibrary cache pin instance (A..Z = namespace)

CFControl file schema global enqueuePFPassword File

CICross-instance function invocation instancePI, PSParallel operation

CUCursor bindPRProcess startup

DFdatafile instanceQA..QZRow cache instance (A..Z = cache)

DLDirect loader parallel index createRTRedo thread global enqueue

DMMount/startup db primary/secondary instanceSCSystem change number instance

DRDistributed recovery processSMSMON

DXDistributed transaction entrySNSequence number instance

FSFile setSQSequence number enqueue

HWSpace management operations on a specific segmentSSSort segment

INInstance numberSTSpace transaction enqueue

IRInstance recovery serialization global enqueueSVSequence number value

ISInstance stateTAGeneric enqueue

IVLibrary cache invalidation instanceTSTemporary segment enqueue (ID2=0)

JQJob queueTSNew block allocation enqueue (ID2=1)

KKThread kickTTTemporary table enqueue

LA .. LPLibrary cache lock instance lock (A..P = namespace)UNUser name

MMMount definition global enqueueUSUndo segment DDL

MRMedia recoveryWLBeing-written redo log instance