DBA_LOCK_INTERNAL

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.

====================================================================



DBA_LOCK_INTERNAL displays a row for each lock or latch that is being held, and one row for each outstanding request for a lock or latch.

Column	Datatype	NULL	Description
SESSION_ID	NUMBER		Session holding or acquiring the lock
LOCK_TYPE	VARCHAR2(56)		Lock type
MODE HELD	VARCHAR2(40)		Lock mode
MODE REQUESTED	VARCHAR2(40)		Lock mode requested
LOCK_ID1	VARCHAR2(1130)		Type-specific lock identifier, part 1
LOCK_ID2	VARCHAR2(40)		Type-specific lock identifier, part 2

Note:

1.

select * from DBA_LOCK_INTERNAL;

SESSION_ID	LOCK_TYPE	MODE_HELD	MODE_REQUESTED	LOCK_ID1	LOCK_ID2
2639	XR	Null	None	4	0
2639	Control File	Row-S (SS)	None	0	0
2375	PL/SQL User Lock	Exclusive	None	1078498312	0
2639	RS	Row-S (SS)	None	25	1
2640	Redo Thread	Exclusive	None	1	0
2642	Media Recovery	Share	None	1	0

2. The value in LOCK_TYPE are Oracle enqueue names.

Oracle Enqueues are shared memory structures (locks) that serialize access to database resources. They can be associated with a session or transaction. Enqueue names are displayed in the LOCK_TYPE column of the DBA_LOCK and DBA_LOCK_INTERNAL data dictionary views.
A resource uniquely identifies an object that can be locked by different sessions within an instance (local resource) or between instances (global resource). Each session that tries to lock the resource will have an enqueue on the resource.

The names of enqueues and their definitions may change from release to release.

The Oracle enqueues are:

· BL, Buffer Cache Management

· BR, Backup/Restore

· CF, Controlfile Transaction

· CI, Cross-instance Call Invocation

· CU, Bind Enqueue

· DF, Datafile

· DL, Direct Loader Index Creation

· DM, Database Mount

· DR, Distributed Recovery Process

· DX, Distributed Transaction

· FP, File Object

· FS, File Set

· HW, High-Water Lock

· IN, Instance Number

· IR, Instance Recovery

· IS, Instance State

· IV, Library Cache Invalidation

· JI, Enqueue used during AJV snapshot refresh

· JQ, Job Queue

· KK, Redo Log "Kick"

· KO, Multiple Object Checkpoint

· L[A-P], Library Cache Lock

· LS, Log Start or Switch

· MM, Mount Definition

· MR, Media Recovery

· N[A-Z], Library Cache Pin

· PE, ALTER SYSTEM SET PARAMETER = VALUE

· PF, Password File

· PI, Parallel Slaves

· PR, Process Startup

· PS, Parallel Slave Synchronization

· Q[A-Z], Row Cache

· RO, Object Reuse

· RT, Redo Thread

· RW, Row Wait

· SC, System Commit Number

· SM, SMON

· SN, Sequence Number

· SQ, Sequence Number Enqueue

· SR, Synchronized Replication

· SS, Sort Segment

· ST, Space Management Transaction

· SV, Sequence Number Value

· TA, Transaction Recovery

· TC, Thread Checkpoint

· TE, Extend Table

· TM, DML Enqueue

· TO, Temporary Table Object Enqueue

· TS, Temporary Segment (also TableSpace)

· TT, Temporary Table

· TX, Transaction

· UL, User-defined Locks

· UN, User Name

· US, Undo Segment, Serialization

· WL, Being Written Redo Log

· XA, Instance Attribute Lock

· XI, Instance Registration Lock

Oracle data dictionary views

Oracle dynamic performance views