ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle session阻塞查询

oracle session阻塞查询

原创 Linux操作系统 作者:perfychi 时间:2013-10-04 17:56:01 0 删除 编辑
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.

ColumnDatatypeDescription
ADDRRAW(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 Table 6-1.

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 Types

System TypeDescriptionSystem TypeDescription

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

datafile 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 change 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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27042095/viewspace-773725/,如需转载,请注明出处,否则将追究法律责任。

下一篇: TX锁查询
请登录后发表评论 登录
全部评论

注册时间:2012-06-06

  • 博文量
    486
  • 访问量
    2716354