ORACLE lock 轉貼

V$LOCK
This view lists the locks currently held by the Oracle database server and outstanding requests for a lock or latch.

Column Datatype Description
ADDR
RAW(4 | 8)
Address of lock state object

KADDR
RAW(4 | 8)
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. The system type locks are listed in Table 3-1.

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


Table 3-1 Values for the TYPE Column: System Types
System Type Description System Type Description
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