Oracle lock types

Did you know that there are 202 different lock types in Oracle 11.2 but only 4 of them which are a direct result of a SQL statement? Here is the list:

TYPE	NAME	IS_USER	DESCRIPTION
TM	DML	YES	Synchronizes accesses to an object
TX	Transaction	YES	Lock held by a transaction to allow other transactions to wait for it
UL	User-defined	YES	Lock used by user applications
ZH	Compression Analyzer	YES	Synchronizes analysis and insert into compression$, prevents multiple threads analyzing the same table during a load
AB	Auto BMR	NO	A general class of locks used by auto BMR for various purposes
AD	ASM Disk AU Lock	NO	Synchronizes accesses to a specific ASM disk AU
AE	Edition Lock	NO	Prevent Dropping an edition in use
AF	Advisor Framework	NO	This enqueue is used to serialize access to an advisor task
AG	Analytic Workspace Generation	NO	Synchronizes generation use of a particular workspace
AK	GES Deadlock Test	NO	Lock used for internal testing
AM	ASM Enqueue	NO	ASM instance general-purpose enqueue
AO	MultiWriter Object Access	NO	Synchronizes access to objects and scalar variables
AR	ASM Relocation Lock	NO	Protects locked extent pointers during ASM file relocation
AS	Service Operations	NO	Synchronizes new service activation
AT	Alter Tablespace	NO	Serializes ‘alter tablespace’ operations
AU	Audit index file	NO	Lock held to synchronize access XML to audit index file
AV	ASM volume locks	NO	id1 is for persistent DG number locking. id2 is for other volume serialization
AW	Analytic Workspace	NO	Synchronizes access to Analytic Workspace resources
AY	KSXA Test Affinity Dictionary	NO	Affinity Dictionary test affinity synchronization
BB	Global Transaction Branch	NO	2PC distributed transaction branch across RAC instances
BF	BLOOM FILTER	NO	Synchronize access to a bloom filter in a parallel statement
BR	Backup/Restore	NO	Lock held by a backup/restore operation to allow other operations to wait for it
CA	Calibration	NO	Lock used by IO Calibration
CF	Controlfile Transaction	NO	Synchronizes accesses to the controlfile
CI	Cross-Instance Call Invocation	NO	Coordinates cross-instance function invocations
CL	Label Security cache	NO	Synchronizes accesses to label cache and label tables
CM	ASM Instance Enqueue	NO	ASM instance and gate enqueue
CN	KTCN REG enq	NO	Enqueue held for registrations for change notifications
CO	KTUCLO Master Slave enq	NO	Enqueue held for determining Master Slaves
CQ	Cleanup querycache registrations	NO	Serializes access to cleanup client query cache registrations
CR	Reuse Block Range	NO	Coordinates checkpointing of block range reuse
CT	Block Change Tracking	NO	A general class of locks used by change tracking for various purposes
CU	Cursor	NO	Recovers cursors in case of death while compiling
DB	DbsDriver	NO	Synchronizes modification of database wide supplementallogging attributes
DD	ASM Local Disk Group	NO	Synchronizes local accesses to ASM disk groups
DF	Datafile Online in RAC	NO	Enqueue held by foreground or DBWR when a datafile is brought online in RAC
DG	ASM Disk Group Modification	NO	Synchronizes accesses to ASM disk groups
DI	GES Internal	NO	Coordinates Global Enqueue Service deadlock detection
DL	Direct Loader Index Creation	NO	Lock to prevent index DDL during direct load
DM	Database Mount/Open	NO	Enqueue held by foreground or DBWR to synchronize database mount/open with other operations
DN	Diskgroup number generator	NO	Serializes Group number generations
DO	ASM Disk Online Lock	NO	Synchronizes disk onlines and their recovery
DP	LDAP Parameter	NO	Synchronizes access to LDAP parameters
DQ	ASM RBAL doorbell	NO	Inter-RBAL process metadata invalidation notification
DR	Distributed Recovery	NO	Serializes the active distributed recovery operation
DS	Database Suspend	NO	Prevents a database suspend during LMON reconfiguration
DT	Default Temporary Tablespace	NO	Serializes changing the default temporary table space and user creation
DV	Diana Versioning	NO	Synchronizes access to lower-version Diana (PL/SQL intermediate representation)
DW	In memory Dispenser	NO	Serialize in memory dispenser operations
DX	Distributed Transaction	NO	Serializes tightly coupled distributed transaction branches
E	Library Cache Lock 2	NO	Synchronizes accesses to library cache objects
FA	ASM File Access Lock	NO	Synchronizes accesses to open ASM files
FB	Format Block	NO	Ensures that only one process can format data blocks in auto segment space managed tablespaces
FC	Disk Group Chunk Mount	NO	controls access to an ACD chunk
FD	Flashback Database	NO	Coordinate flashback database
FE	KTFA Recovery	NO	Flashback archive Enqueue to serialize recovery
FG	ACD Relocation Gate Enqueue	NO	ACD relocation serialization
FL	Flashback database log	NO	Synchronize access to flashback database log
FM	File Mapping	NO	Synchronizes access to global file mapping state
FP	File Object	NO	Synchronizes various File Object(FOB) operations
FR	Disk Group Recovery	NO	enqueue to control ASM recovery
FS	File Set / Dictionary Check	NO	Enqueue used to synchronize recovery and file operations or synchronize dictionary check
FT	Disk Group Redo Generation	NO	controls the privilege to generate redo in a thread
FU	DBFUS	NO	This enqueue is used to serialize the capture of the DB Feature Usage and High Water Mark Statistics
FW	Flashback Writer	NO	Coordinate RVWR on multiple instances
FX	ACD Xtnt Info CIC	NO	serialize ACD relocation CIC
FZ	ASM Freezing Cache Lock	NO	Freezes ASM Cache for a diskgroup
G	Library Cache Pin 2	NO	Synchronizes accesses to the contents of library cache objects
HD	ASM Disk Header	NO	Serializes accesses to ASM SGA data structures
HP	Queue Page	NO	Synchronizes accesses to queue pages
HQ	Hash Queue	NO	Synchronizes the creation of new queue IDs
HV	Direct Loader High Water Mark	NO	Lock used to broker the high water mark during parallel inserts
HW	Segment High Water Mark	NO	Lock used to broker the high water mark during parallel inserts
IA	Internal	NO
ID	NID	NO	Lock held by a NID operation to allow other operations to wait for it
IL	Label Security	NO	Synchronizes accesses to internal label data structures
IM	Kti blr lock	NO	Serializes block recovery for an IMU txn
IR	Instance Recovery	NO	Synchronizes instance recovery
IS	Instance State	NO	Enqueue used to synchronize instance state changes
IT	In-Mem Temp Table Meta Creation	NO	Synchronizes accesses to a temp object’s metadata
IV	Library Cache Invalidation	NO	Synchronizes library cache object invalidations across instances
IZ	INSTANCE LOCK	NO	Proctects the lock name space used by the instance
JD	Job Queue Date	NO	Synchronizes dates between job queue coordinator and slave processes
JI	Materialized View	NO	Lock held during materialized view operations (like refresh, alter) to prevent concurrent operations on the same materialized view
JQ	Job Queue	NO	Lock to prevent multiple instances from running a single job
JS	Job Scheduler	NO	Synchronizes accesses to the job cache
JX	SQL STATEMENT QUEUE	NO	synchronize SQL statement queue operations
KD	Scheduler Master DBRM	NO	Determine DBRM master
KE	ASM Cached Attributes	NO	Synchronization of ASM cached attributes
KK	Kick Instance to Switch Logs	NO	Lock held by open redo thread, used by other instances to force a log switch
KL	LOB KSI LOCK	NO	KSI lock for buffer cache and wgc concurrency
KM	Scheduler	NO	Synchronizes various Resource Manager operations
KO	Multiple Object Checkpoint	NO	Coordinates checkpointing of multiple objects
KP	Kupp Process Startup	NO	Synchronizes kupp process startup
KQ	ASM Attributes Enque	NO	Single Inst Sync of ASM attributes
KT	Scheduler Plan	NO	Synchronizes accesses to the current Resource Manager plan
L	Library Cache Lock 1	NO	Synchronizes accesses to library cache objects
MD	Materialized View Log DDL	NO	Lock held during materialized view log DDL statements
MH	AQ Notification Mail Host	NO	Lock used for recovery when setting Mail Host for AQ e-mail notifications
MK	Master Key	NO	Serializes enc$ access
ML	AQ Notification Mail Port	NO	Lock used for recovery when setting Mail Port for AQ e-mail notifications
MN	LogMiner	NO	Synchronizes updates to the LogMiner dictionary and prevents multiple instances from preparing the same LogMiner session
MO	MMON restricted session	NO	Serialize MMON operations for restricted sessions
MR	Media Recovery	NO	Lock used to coordinate media recovery with other uses of datafiles
MS	Materialized View Refresh Log	NO	Lock held during materialized view refresh to setup MV log
MV	Online Datafile Move	NO	Synchronizes online datafile move operation or cleanup
MW	MWIN Schedule	NO	This enqueue is used to serialize the calibration of the manageability schedules with the Maintenance Window
MX	ksz synch	NO	Used to synchronize storage server info across all nodes
N	Library Cache Pin 1	NO	Synchronizes accesses to the contents of library cache objects
OC	Outline Cache	NO	Synchronizes write accesses to the outline cache
OD	Online DDLs	NO	Lock to prevent concurrent online DDLs
OL	Outline Name	NO	Synchronizes accesses to a particular outline name
OQ	OLAPI Histories	NO	Synchronizes access to olapi history resources
OW	Encryption Wallet	NO	Serializes wallet initialization and access
PD	Property Lock	NO	Serializes property update
PE	Parameter	NO	Synchronizes system parameter updates
PF	Password File	NO	Synchronizes accesses to the password file
PG	Global Parameter	NO	Synchronizes global system parameter updates
PH	AQ Notification Proxy	NO	Lock used for recovery when setting Proxy for AQ HTTP notifications
PI	Remote PX Process Spawn Status	NO	Communicates remote Parallel Execution Server Process creation status
PL	Transportable Tablespace	NO	Coordinates plug-in operation of transportable tablespaces
PM	ASM PST Signalling	NO	Signals inter-instance access to ASM PST metadata
PR	Process Startup	NO	Synchronizes process startup
PS	PX Process Reservation	NO	Parallel Execution Server Process reservation and synchronization
PT	ASM Partnership and Status Table	NO	Gates inter-node synchronization of ASM PST metadata
PV	KSV slave startup	NO	Synchronizes slave start_shut
PW	Buffer Cache PreWarm	NO	Coordinates Direct Loads with Prewarmed cache buffers
Q	Row Cache	NO	Coordinates updates and accesses to row cache objects
RB	ASM Rollback Recovery	NO	Serializes ASM rollback recovery operations
RC	Result Cache: Enqueue	NO	Accessing a result in the result-set cache
RD	RAC Load	NO	synchronize update of RAC load info
RE	Block Repair/Resilvering	NO	Synchronizes block repair/mirror resilvering operations
RF	Data Guard Broker	NO	Synchronizes broker lock operation involving lock value
RK	wallet_set_mkey	NO	Serializes wallet master key rekey
RL	RAC Encryption Wallet Lock	NO	RAC encryption wallet lock
RM	GES Resource Remastering	NO	Coordinates Global Enqueue Service resource remastering
RN	Redo Log Nab Computation	NO	Coordinates nab computations of online logs during recovery
RO	Multiple Object Reuse	NO	Coordinates flushing of multiple objects
RP	Resilver / Repair	NO	Enqueue held when resilvering is needed or when datablock is repaired from mirror
RR	Workload Capture and Replay	NO	Prevents concurrent invokers of DBMS_WORKLOAD_*
RS	Reclaimable Space	NO	Lock held by a space reclaimable operation to allow other operations to wait for it
RT	Redo Thread	NO	Thread locks held by LGWR, DBW0, and RVWR to indicate mounted or open status
RU	Rolling Migration	NO	Serilized rolling migration operations
RW	Materialized View Flags	NO	Lock used when creating or readingmaterialized view flags in detail tables
RX	ASM Extent Relocation Lock	NO	Synchronizes relocating ASM extents
SB	LogicalStandby	NO	Synchronizes Logical Standby metadata operations
SC	System Change Number	NO	Coordinates system change number generation on multiple instances
SE	Session Migration	NO	Lock used by transparent session migration
SF	AQ Notification Sender	NO	Lock used for recovery when setting Sender for AQ e-mail notifications
SH	Active Session History Flushing	NO	To prevent multiple concurrent flushers
SI	Streams Table Instantiation	NO	Prevents muiltiple streams table instantiations
SJ	KTSJ Slave Task Cancel	NO	Serializes cancelling task executed by slave process
SK	Shrink Segment	NO	Serialize shrink of a segment
SL	Serialize Lock request	NO	Request serialization to LCK0
SM	SMON Serialization	NO	Lock to check SMON global work in RAC
SO	Shared Object	NO	Synchronizes access to Shared Object(PL/SQL Shared Object Manager)
SP	Spare Enqueue	NO	Spare enqueue for one-off backports
SQ	Sequence Cache	NO	Lock to ensure that only one process can replenish the sequence cache
SR	Synchronized Replication	NO	Coordinates replication / streams operations
SS	Sort Segment	NO	Ensures that sort segments created during parallel DML operations aren’t prematurely cleaned up
ST	Space Transaction	NO	Synchronizes space management activities in dictionary-managed tablespaces
SU	SaveUndo Segment	NO	Serializes access to SaveUndo Segment
SV	Sequence Ordering	NO	Lock to ensure ordered sequence allocation in RAC mode
SW	Suspend Writes	NO	Coordinates the ‘alter system suspend’ operation
TA	Instance Undo	NO	Serializes operations on undo segments and undo tablespaces
TB	SQL Tuning Base Existence Cache	NO	Synchronizes writes to the SQL Tuning Base Existence Cache
TC	Tablespace Checkpoint	NO	Lock held to guarantee uniqueness of a tablespace checkpoint
TD	KTF map table enqueue	NO	Serializes updates and inserts to the SMON_SCN_TIME mapping table
TE	KTF broadcast	NO	Serializes broadcasts for flushes to SMON_SCN_TIME
TF	Temporary File	NO	Serializes dropping of a temporary file
TH	Threshold Chain	NO	Serializes threshold in-memory chain access
TK	Auto Task Serialization	NO	Prevents more than one AutoTask Background Slave from being spawned
TL	Log Lock	NO	Serializes threshold log table read and update
TO	Temp Object	NO	Synchronizes DDL and DML operations on a temp object
TP	Runtime Fixed Table Purge	NO	Lock used for fixed runtime table purge and definition
TQ	Queue table enqueue	NO	Synchronizes access to queue table
TS	Temporary Segment	NO	Serializes accesses to temp segments
TT	Tablespace	NO	Serializes DDL operations on tablespaces
TW	Cross-Instance Transaction	NO	Lock held by one instance to wait for transactions on all instances to finish
US	Undo Segment	NO	Lock held to perform DDL on the undo segment
V	Library Cache Lock 3	NO	Synchronizes accesses to library cache objects
WA	AQ Notification Watermark	NO	Lock used for recovery when setting Watermark for memory usage in AQ notifications
WF	AWR Flush	NO	This enqueue is used to serialize the flushing of snapshots
WG	Write gather local enqueue	NO	Long term lock on wgc file state
WL	Being Written Redo Log	NO	Coordinates access to redo log files and archive logs
WM	WLM Plan Operations	NO	Synchronizes new WLM Plan activation
WP	AWR Purge	NO	This enqueue handles concurrency between purging and baselines
WR	LNS archiving log	NO	Coordinates access to logs by Async LNS and ARCH/FG
WS	LogWriter Standby	NO	Used by LogWriter to coordinate communication to standby databases
XB	ASM Group Block lock	NO	Prevents client diskgroup use during storage reconfiguration
XC	XDB Configuration	NO	Synchronization access to XDB configuration
XD	Auto Online Exadata disks	NO	Serialize Auto Management of Exadata disks
XH	AQ Notification No-Proxy	NO	Lock used for recovery when setting No Proxy Domains for AQ HTTP notifications
XL	ASM Extent Fault Lock	NO	Keep multiple processes from faulting in the same extent chunk
XQ	ASM Extent Relocation Enqueue	NO	ASM extent relocation
XR	Quiesce / Force Logging	NO	Lock held during database quiesce or for database force logging
XY	Internal Test	NO	Lock used for internal testing
Y	Library Cache Pin 3	NO	Synchronizes accesses to the contents of library cache objects
ZA	Audit Partition	NO	Lock held for adding partition to Aud table
ZF	FGA Partition	NO	Lock held for adding partition to Fga table
ZG	File Group	NO	Coordinates file group operations
ZZ	Global Context Action	NO	Lock held for updating Global context hash tables

The SQL to retrieve them:

SELECT type, name, is_user, description FROM V$LOCK_TYPE ORDER BY is_user DESC, type;

About these ads