Venzi's Tech-Blog

19 August 2013

Oracle 12c enqueue waits

Filed under: Oracle — Venzi @ 16:28
Tags: ,

Just as with the new Oracle 12c lock types it’s time to take a quick look at the new enqueue waits in 12c. Oracle 11.2.0.3 had a total of 304 enqueue waits. 12c has a total of 418. A lot of work seems also to have happened on ASM in this release. The amount of new enqueues per group looks like following:

Group Amount
ASM Enqueue 16
Enqueue-Cross SCN 12
Backup/Restore 11
Queue Ptning global enq 8
Being Written Redo Log 8
SGA Log-Bkt Flush 6
AM Container 5
Media Recovery 5
OLS groups 4
Instance Recovery 3
AVM RTA Access 3
AM Contained File Identification 3
Privilege Capture API 2
Queue table enqueue 2
Label Security Profile 2
RFS Logminer FLC 2
Data Guard Broker 2
IOServer File 2
Queue Partitioning local enqueue 2
Column Key 1
Flush Stat 1
Pluggable Database 1
SGA Log Operation 1
AM Group Rebuild/Validate 1
Real-Time ADDM Flood Control 1
AM Contained File Resize 1
PDB DDL Operations 1
non durable sub bmap alloc 1
CBAC Master Lock 1
IOServer Client 1
PDB Instance State 1
Oracle File System Operation 1
KJCI Serialization Enqueue 1
Spillover Audit files 1
Application Continuity 1
File Set / Dictionary Check 1

Here is the list of all the new enqueues in Oracle 12c:

Group Enqueue Type Description
Application Continuity enq: AC – acquiring partition id Synchronizes partition id
ASM Enqueue enq: AM – ASM ATB COD creation ASM ATB COD creation
ASM Enqueue enq: AM – ASM Audit file Cleanup ASM Audit file Cleanup
ASM Enqueue enq: AM – ASM Audit file Delete ASM Audit file Delete
ASM Enqueue enq: AM – ASM DD update SrRloc Serializes DD update for SrRloc
ASM Enqueue enq: AM – ASM client check Synchronizes client check, kill and register
ASM Enqueue enq: AM – ASM client operation Synchronizes client operations and shutdown
ASM Enqueue enq: AM – ASM file access req Synchronizes file access per client
ASM Enqueue enq: AM – ASM file chown Prevents new owners of open files from being dropped
ASM Enqueue enq: AM – ASM metadata replication Serializes metadata replication
ASM Enqueue enq: AM – Create default DG key in OCR ASM audit create default DG key in OCR
ASM Enqueue enq: AM – Credential creation Lock held while creating credentials
ASM Enqueue enq: AM – Credential deletion Lock held while deleting credentials
ASM Enqueue enq: AM – Disable Remote ASM Lock held while disabling remote asm
ASM Enqueue enq: AM – Enable Remote ASM Lock held while enabling remote asm
ASM Enqueue enq: AM – Register with IOServer Serializes the registration of the database with IOServer
ASM Enqueue enq: AM – SR slice Clear/Mark Serialize clear/assignment for SR slices
Queue Partitioning local enqueue enq: AQ – QPT fg map dqpt kwsptGetOrMapDqPtn
Queue Partitioning local enqueue enq: AQ – QPT fg map qpt kwsptGetOrMapQPtn
non durable sub bmap alloc enq: BA – SUBBMAP contention subscriber access to bitmap
AM Container enq: BC – create container Container lock held while creating a container
AM Container enq: BC – drop container Container lock held while dropping a container
AM Container enq: BC – drop container group Group lock held while dropping a container group
AM Container enq: BC – group – create container Group lock held while creating a container
AM Container enq: BC – group – create file Group lock held while creating a contained file
AM Contained File Identification enq: BI – create file Enqueue held while a contained file is created
AM Contained File Identification enq: BI – delete file Enqueue held while a contained file is cleaned up or deleted
AM Contained File Identification enq: BI – identify file Enqueue held while a contained file is identified
Backup/Restore enq: BR – RS check quiescence Lock to verify that task execution is not being quiesced
Backup/Restore enq: BR – RS invalidate plans Lock to allow invalidation of recovery server plans
Backup/Restore enq: BR – RS lock API Lock to single stream API functions on recovery server
Backup/Restore enq: BR – RS lock db Perform DB storage allocations on recovery server
Backup/Restore enq: BR – RS lock scheduler Lock to perform job scheduling functions on recovery server
Backup/Restore enq: BR – RS lock storage loc Lock to use reserved storage to perform in line purging
Backup/Restore enq: BR – RS lock timer queue Lock to perform timer functions on recovery server
Backup/Restore enq: BR – RS protect plans Lock to protect a recovery server plan from becoming invalid
Backup/Restore enq: BR – RS purge storage loc Lock to restrict a single purging thread per storage location
Backup/Restore enq: BR – RS quiesce storage Lock to prevent new tasks from executing
Backup/Restore enq: BR – RS run scheduler Hint that recovery server schedulers may have new work
AM Group Rebuild/Validate enq: BV – rebuild/validate group Enqueue held while a container group is rebuilding
AM Contained File Resize enq: BZ – resize file Enqueue held while a contained file is resized
CBAC Master Lock enq: CB – role operation Synchronizes accesses to the CBAC roles cached in KGL
Column Key enq: CC – decryption decrypting and caching column key
Pluggable Database enq: CP – Pluggable database resetlogs Synchronization
Flush Stat enq: FH – contention Serializes flush of ILM stats to disk
Oracle File System Operation enq: FO – file system operation contention Synchronizes various Oracle File system operations
File Set / Dictionary Check enq: FS – online log operation Enqueue used to synchronize LGWR cached open online logs and online log clear/drop
IOServer Client enq: IC – IOServer clientID Gets a unique client ID
IOServer File enq: IF – file close File Close
IOServer File enq: IF – file open File Open
PDB Instance State enq: IP – open/close PDB Enqueue used to synchronize instance state changes for PDBs
Instance Recovery enq: IR – arbitrate instance recovery arbitrate parallel instance recovery
Instance Recovery enq: IR – global serialization serialize instance recovery globally
Instance Recovery enq: IR – local serialization serialize instance recovery with local dismount
KJCI Serialization Enqueue enq: KI – contention Synchronizes Cross-Instance Calls
SGA Log Operation enq: MC – Securefile log Serializes log creation/destruction with log flushes
SGA Log-Bkt Flush enq: MF – creating swap in instance Serializes multiple processes in creating the swap space
SGA Log-Bkt Flush enq: MF – flush bkgnd periodic Serializes flushes for a SGA log in bkgnd
SGA Log-Bkt Flush enq: MF – flush client Serializes flushes for a single SGA log – client
SGA Log-Bkt Flush enq: MF – flush destroy Serializes flushes for a single SGA log – destroy
SGA Log-Bkt Flush enq: MF – flush prior error Serializes flushes for a single SGA log – error earlier
SGA Log-Bkt Flush enq: MF – flush space Serializes flushes for a single SGA log – space lack
Media Recovery enq: MR – PDB open Lock used to coordinate media recovery with other uses of PDB datafiles
Media Recovery enq: MR – adg instance recovery Synchronizes multi instance recovery cleanup with adg instance recovery
Media Recovery enq: MR – any role transition Lock used to disallow any concurrent role transition attempt
Media Recovery enq: MR – datafile online Enqueue used to coordinate datafile offline in RAC
Media Recovery enq: MR – multi instance redo apply Lock used to disallow concurrent recovery attempt when MIRA active
Label Security Profile enq: OP – OLS Cleanup unused profiles Synchronizing access to ols$profile when deleting unused profiles
Label Security Profile enq: OP – OLS Store user entries Synchronizing access to ols$user when inserting user entries
Privilege Capture API enq: PA – modify a privilege capture lock held for during modify a privilege capture
Privilege Capture API enq: PA – read a privilege capture lock held for during reading privilege captur status
PDB DDL Operations enq: PB – PDB Lock Enqueue used to synchronize PDB DDL operations
Queue Ptning global enq enq: PQ – QPT LB Trunc sync kwslbFreShadowShrd:LB syncronization with Truncate
Queue Ptning global enq enq: PQ – QPT Trunc kwsptChkTrncLst:Truncate
Queue Ptning global enq enq: PQ – QPT add dqpt kwsptLoadDqCache: Add DQ Partitions.
Queue Ptning global enq enq: PQ – QPT add dqpt fg kwsptMapDqPtn:Drop DQ Partitions in foreground
Queue Ptning global enq enq: PQ – QPT add qpt kwsptLoadQCache: Add Q Partitions.
Queue Ptning global enq enq: PQ – QPT add qpt fg kwsptMapQPtn: Add Q Partitions in foreground
Queue Ptning global enq enq: PQ – QPT drop dqpt kwsptLoadDqCache:Drop DQ Partitions.
Queue Ptning global enq enq: PQ – QPT drop qpt kwsptLoadQCache:Drop Q Partitions.
AVM RTA Access enq: PY – AVM RTA access database Database RTA info access on AVM
AVM RTA Access enq: PY – AVM RTA access database 2 Database RTA info access on AVM
AVM RTA Access enq: PY – AVM RTA access instance Instance RTA info access on AVM
Real-Time ADDM Flood Control enq: RA – RT ADDM flood control Flood control in RAC. Acquired in no-wait.
Data Guard Broker enq: RF – Broker State Lock Captures database-wide broker state
Data Guard Broker enq: RF – FSFO string buffer Makes a FSFO-related string available across RAC instances
Enqueue-Cross SCN enq: RQ – AQ indexed cached commit AQ indexed cached commit
Enqueue-Cross SCN enq: RQ – AQ uncached commit WM update AQ uncached commit WM update
Enqueue-Cross SCN enq: RQ – AQ uncached dequeue AQ uncached dequeue
Enqueue-Cross SCN enq: RQ – Cross export subshard Cross(export) – truncate subshard
Enqueue-Cross SCN enq: RQ – Cross import subshard Cross(import) – free shadow shard
Enqueue-Cross SCN enq: RQ – Cross update scn Cross process updating disk
Enqueue-Cross SCN enq: RQ – Dequeue update scn Dequeue updating scn
Enqueue-Cross SCN enq: RQ – Enqueue commit cached Enqueue commit rac cached
Enqueue-Cross SCN enq: RQ – Enqueue commit uncached Enqueue commit uncached
Enqueue-Cross SCN enq: RQ – Free shadow shard Free shadow – Cross(import) shard
Enqueue-Cross SCN enq: RQ – Parallel cross update scn Parallel cross(update scn) – truncate subshard
Enqueue-Cross SCN enq: RQ – Trucate subshard Truncate – Cross(export) subshard
RFS Logminer FLC enq: RZ – add element Synchronizes access to the foreign log cache while a structure is being inserted
RFS Logminer FLC enq: RZ – remove element Synchronizes access to the foreign log cache while a structure is being removed
OLS groups enq: SG – OLS Add Group Synchronize access to ols$groups when zlllabGroupTreeAddGroup does a read
OLS groups enq: SG – OLS Alter Group Parent Synchronizing access to ols$groups when alter group parent
OLS groups enq: SG – OLS Create Group Synchronize access to ols$groups when creating a group
OLS groups enq: SG – OLS Drop Group Synchronizing access to ols$groups when dropping a group
Queue table enqueue enq: TQ –  sq TM contention SQ TM access to the queue table
Queue table enqueue enq: TQ – INI sq contention INIT access to the queue tables
Being Written Redo Log enq: WL – Far Sync Fail Over Exclude multiple simultaneous failovers
Being Written Redo Log enq: WL – Switchover To Primary One and only one process executing switchover to primary
Being Written Redo Log enq: WL – redo_db table query Synchronize query to redo_db table
Being Written Redo Log enq: WL – redo_db table update Serialize update to redo_db table
Being Written Redo Log enq: WL – redo_log table query Synchronize query to redo_log table
Being Written Redo Log enq: WL – redo_log table update Serialize update to redo_log table
Being Written Redo Log enq: WL – redo_rta table query Synchronize query to redo_rta table
Being Written Redo Log enq: WL – redo_rta table update Serialize update to redo_rta table
Spillover Audit files enq: ZS – excl access to spill audit file lock held while writing to/renaming/deleting spillover audit file

However, one enqueue wait disappeared in 12c which was the “enq: DO – disk online operation”:

Group Enqueue Type Description
ASM Disk Online Lock enq: DO – disk online operation Represents an active disk online operation

It doesn’t seem that it got replaced by any of the new enqueues, at least when looking at the names and descriptions. So it is likely that it is simply not required anymore. A lot of work went into the new release making sure that operations can now be done online which would have required locks/enqueues in the past or simply would have made parts of the schema unusable (especially when it comes to partitioning and index maintenance).

Below is the complete list of enqueue waits in Oracle 12c:

Group Enqueue Type Description
Auto BMR enq: AB – ABMR process initialized Lock held to ensure that ABMR process is initialized
Auto BMR enq: AB – ABMR process start/stop Lock held to ensure that only one ABMR is started in the cluster
Application Continuity enq: AC – acquiring partition id Synchronizes partition id
ASM Disk AU Lock enq: AD – allocate AU Synchronizes accesses to a specific ASM disk AU
ASM Disk AU Lock enq: AD – deallocate AU Synchronizes accesses to a specific ASM disk AU
ASM Disk AU Lock enq: AD – relocate AU Synchronizes accesses to a specific ASM disk AU
Edition Lock enq: AE – lock Prevent Dropping an edition in use
Advisor Framework enq: AF – task serialization This enqueue is used to serialize access to an advisor task
Analytic Workspace Generation enq: AG – contention Synchronizes generation use of a particular workspace
ASM Enqueue enq: AM – ASM ACD Relocation Block ASM cache freeze
ASM Enqueue enq: AM – ASM ATB COD creation ASM ATB COD creation
ASM Enqueue enq: AM – ASM Amdu Dump Allow only one AMDU dump when block read failure
ASM Enqueue enq: AM – ASM Audit file Cleanup ASM Audit file Cleanup
ASM Enqueue enq: AM – ASM Audit file Delete ASM Audit file Delete
ASM Enqueue enq: AM – ASM DD update SrRloc Serializes DD update for SrRloc
ASM Enqueue enq: AM – ASM File Destroy Prevent same file deletion race
ASM Enqueue enq: AM – ASM Grow ACD Serializes growing ASM ACD
ASM Enqueue enq: AM – ASM Password File Update Allow one ASM password file update per cluster at a time
ASM Enqueue enq: AM – ASM User Prevents a user from being dropped if it owns any open files
ASM Enqueue enq: AM – ASM cache freeze Start ASM cache freeze
ASM Enqueue enq: AM – ASM client check Synchronizes client check, kill and register
ASM Enqueue enq: AM – ASM client operation Synchronizes client operations and shutdown
ASM Enqueue enq: AM – ASM disk based alloc/dealloc Synchronizes disk based allocations/deallocations
ASM Enqueue enq: AM – ASM file access req Synchronizes file access per client
ASM Enqueue enq: AM – ASM file chown Prevents new owners of open files from being dropped
ASM Enqueue enq: AM – ASM file descriptor Serializes access to ASM file descriptors
ASM Enqueue enq: AM – ASM file relocation Serializes file relocation shrink and drop
ASM Enqueue enq: AM – ASM metadata replication Serializes metadata replication
ASM Enqueue enq: AM – ASM reserved Check id1 of call for specific purpose
ASM Enqueue enq: AM – Create default DG key in OCR ASM audit create default DG key in OCR
ASM Enqueue enq: AM – Credential creation Lock held while creating credentials
ASM Enqueue enq: AM – Credential deletion Lock held while deleting credentials
ASM Enqueue enq: AM – Disable Remote ASM Lock held while disabling remote asm
ASM Enqueue enq: AM – Enable Remote ASM Lock held while enabling remote asm
ASM Enqueue enq: AM – Register with IOServer Serializes the registration of the database with IOServer
ASM Enqueue enq: AM – SR slice Clear/Mark Serialize clear/assignment for SR slices
ASM Enqueue enq: AM – background COD reservation Reserve a background COD entry
ASM Enqueue enq: AM – block repair Serializes block repairs
ASM Enqueue enq: AM – client registration Registers DB instance to ASM client state object hash
ASM Enqueue enq: AM – disk offline Synchronizes disk offlines
ASM Enqueue enq: AM – group block ASM group block
ASM Enqueue enq: AM – group use Client group use
ASM Enqueue enq: AM – rollback COD reservation Reserve a rollback COD entry
ASM Enqueue enq: AM – shutdown Prevent DB instance registration during ASM instance shutdown
MultiWriter Object Access enq: AO – contention Synchornizes access to objects and scalar variables
SPM Autopurge enq: AP – contention This enqueue is used to serialize the purging of SQL plan baselines
Queue Partitioning local enqueue enq: AQ – QPT fg map dqpt kwsptGetOrMapDqPtn
Queue Partitioning local enqueue enq: AQ – QPT fg map qpt kwsptGetOrMapQPtn
Service Operations enq: AS – service activation Synchronizes new service activation
Alter Tablespace enq: AT – contention Serializes ‘alter tablespace’ operations
ASM volume locks enq: AV – AVD client registration Serialize inst reg and first DG use
ASM volume locks enq: AV – add/enable first volume in DG Serialize taking the AVD DG enqueue
ASM volume locks enq: AV – persistent DG number prevent DG number collisions
ASM volume locks enq: AV – volume relocate Serialize relocating volume extents
Analytic Workspace enq: AW – AW generation lock In-use generation state for a particular workspace
Analytic Workspace enq: AW – AW state lock Row lock synchronization for the AW$ table
Analytic Workspace enq: AW – AW$ table lock Global access synchronization to the AW$ table
Analytic Workspace enq: AW – user access for AW Synchronizes user accesses to a particular workspace
KSXA Test Affinity Dictionary enq: AY – contention Affinity Dictionary test affinity synchronization
non durable sub bmap alloc enq: BA – SUBBMAP contention subscriber access to bitmap
Global Transaction Branch enq: BB – 2PC across RAC instances 2PC distributed transaction branch across RAC instances
AM Container enq: BC – create container Container lock held while creating a container
AM Container enq: BC – drop container Container lock held while dropping a container
AM Container enq: BC – drop container group Group lock held while dropping a container group
AM Container enq: BC – group – create container Group lock held while creating a container
AM Container enq: BC – group – create file Group lock held while creating a contained file
BLOOM FILTER enq: BF – PMON Join Filter cleanup PMON bloom filter recovery
BLOOM FILTER enq: BF – allocation contention Allocate a bloom filter in a parallel statement
AM Contained File Identification enq: BI – create file Enqueue held while a contained file is created
AM Contained File Identification enq: BI – delete file Enqueue held while a contained file is cleaned up or deleted
AM Contained File Identification enq: BI – identify file Enqueue held while a contained file is identified
clonedb bitmap file access enq: BM – clonedb bitmap file write synchronizes clonedb bitmap file operations
Backup/Restore enq: BR – RS check quiescence Lock to verify that task execution is not being quiesced
Backup/Restore enq: BR – RS invalidate plans Lock to allow invalidation of recovery server plans
Backup/Restore enq: BR – RS lock API Lock to single stream API functions on recovery server
Backup/Restore enq: BR – RS lock db Perform DB storage allocations on recovery server
Backup/Restore enq: BR – RS lock scheduler Lock to perform job scheduling functions on recovery server
Backup/Restore enq: BR – RS lock storage loc Lock to use reserved storage to perform in line purging
Backup/Restore enq: BR – RS lock timer queue Lock to perform timer functions on recovery server
Backup/Restore enq: BR – RS protect plans Lock to protect a recovery server plan from becoming invalid
Backup/Restore enq: BR – RS purge storage loc Lock to restrict a single purging thread per storage location
Backup/Restore enq: BR – RS quiesce storage Lock to prevent new tasks from executing
Backup/Restore enq: BR – RS run scheduler Hint that recovery server schedulers may have new work
Backup/Restore enq: BR – file shrink Lock held to prevent file from decreasing in physical size during RMAN backup
Backup/Restore enq: BR – multi-section restore header Lock held to serialize file header access during multi-section restore
Backup/Restore enq: BR – multi-section restore section Lock held to serialize section access during multi-section restore
Backup/Restore enq: BR – perform autobackup Lock held to perform a new controlfile autobackup
Backup/Restore enq: BR – proxy-copy Lock held to allow cleanup from backup mode during an RMAN proxy-copy backup
Backup/Restore enq: BR – request autobackup Lock held to request controlfile autobackups
Backup/Restore enq: BR – space info datafile hdr update Lock held to prevent multiple process to update the headers at the same time
AM Group Rebuild/Validate enq: BV – rebuild/validate group Enqueue held while a container group is rebuilding
AM Contained File Resize enq: BZ – resize file Enqueue held while a contained file is resized
Calibration enq: CA – contention Synchronizes various IO calibration runs
CBAC Master Lock enq: CB – role operation Synchronizes accesses to the CBAC roles cached in KGL
Column Key enq: CC – decryption decrypting and caching column key
Controlfile Transaction enq: CF – contention Synchronizes accesses to the controlfile
Cross-Instance Call Invocation enq: CI – contention Coordinates cross-instance function invocations
Label Security cache enq: CL – compare labels Synchronizes accesses to label cache for label comparison
Label Security cache enq: CL – drop label Synchronizes accesses to label cache when dropping a label
ASM Instance Enqueue enq: CM – diskgroup dismount serialize asm diskgroup dismount
ASM Instance Enqueue enq: CM – gate serialize access to instance enqueue
ASM Instance Enqueue enq: CM – instance indicate ASM diskgroup is mounted
KTCN REG enq enq: CN – race with init during descriptor initialization
KTCN REG enq enq: CN – race with reg during transaction commit to see concurrent registrations
KTCN REG enq enq: CN – race with txn during registration
KTUCLO Master Slave enq enq: CO – master slave det enqueue held be Master in Cleanout Optim
Pluggable Database enq: CP – Pluggable database resetlogs Synchronization
Cleanup querycache registrations enq: CQ – contention Serializes access to cleanup client query cache registrations
Reuse Block Range enq: CR – block range reuse ckpt Coordinates fast block range reuse ckpt
Block Change Tracking enq: CT – CTWR process start/stop Lock held to ensure that only one CTWR process is started in a single instance
Block Change Tracking enq: CT – change stream ownership Lock held by one instance while change tracking is enabled, to guarantee access to thread-specific resources
Block Change Tracking enq: CT – global space management Lock held during change tracking space management operations that affect the entire change tracking file
Block Change Tracking enq: CT – local space management Lock held during change tracking space management operations that affect just the data for one thread
Block Change Tracking enq: CT – reading Lock held to ensure that change tracking data remains in existence until a reader is done with it
Block Change Tracking enq: CT – state Lock held while enabling or disabling change tracking, to ensure that it is only enabled or disabled by one user at a time
Block Change Tracking enq: CT – state change gate 1 Lock held while enabling or disabling change tracking in RAC
Block Change Tracking enq: CT – state change gate 2 Lock held while enabling or disabling change tracking in RAC
Cursor enq: CU – contention Recovers cursors in case of death while compiling
TEXT: Index Specific Lock enq: CX – TEXT: Index Specific Lock Index Specific Lock on CTX index
DbsDriver enq: DB – contention Synchronizes modification of database wide supplementallogging attributes
ASM Local Disk Group enq: DD – contention Synchronizes local accesses to ASM disk groups
Datafile Online in RAC enq: DF – contention Enqueue held by foreground or DBWR when a datafile is brought online in RAC
ASM Disk Group Modification enq: DG – contention Synchronizes accesses to ASM disk groups
Direct Loader Index Creation enq: DL – contention Lock to prevent index DDL during direct load
Database Mount/Open enq: DM – contention Enqueue held by foreground or DBWR to synchronize database mount/open or pluggable database open with other operations
Diskgroup number generator enq: DN – contention Serializes group number generations
ASM Disk Online Lock enq: DO – Staleness Registry create Synchronizes Staleness Registry creation
ASM Disk Online Lock enq: DO – disk online Synchronizes disk onlines and their recovery
ASM Disk Online Lock enq: DO – disk online recovery Synchronizes disk onlines and their recovery
ASM Disk Online Lock enq: DO – startup of MARK process Synchronizes startup of MARK process
LDAP Parameter enq: DP – contention Synchronizes access to LDAP parameters
Distributed Recovery enq: DR – contention Serializes the active distributed recovery operation
Database Suspend enq: DS – contention Prevents a database suspend during LMON reconfiguration
Default Temporary Tablespace enq: DT – contention Serializes changing the default temporary table spaceand user creation
Diana Versioning enq: DV – contention Synchronizes access to lower-version Diana (PL/SQL intermediate representation)
In memory Dispenser enq: DW – contention Serialize in memory dispenser operations
Distributed Transaction enq: DX – contention Serializes tightly coupled distributed transaction branches
ASM File Access Lock enq: FA – access file Synchronizes accesses to open ASM files
Format Block enq: FB – contention Ensures that only one process can format data blcoks in auto segment space managed tablespaces
Disk Group Chunk Mount enq: FC – open an ACD thread LGWR opens an ACD thread
Disk Group Chunk Mount enq: FC – recover an ACD thread SMON recovers an ACD thread
Flashback Database enq: FD – Flashback coordinator Synchronization
Flashback Database enq: FD – Flashback logical operations Synchronization
Flashback Database enq: FD – Flashback on/off Synchronization
Flashback Database enq: FD – Marker generation Synchronization
Flashback Database enq: FD – Restore point create/drop Synchronization
Flashback Database enq: FD – Tablespace flashback on/off Synchronization
KTFA Recovery enq: FE – contention Serializes flashback archive recovery
ACD Relocation Gate Enqueue enq: FG – FG redo generation enq race resolve race condition to acquire Disk Group Redo Generation Enqueue
ACD Relocation Gate Enqueue enq: FG – LGWR redo generation enq race resolve race condition to acquire Disk Group Redo Generation Enqueue
ACD Relocation Gate Enqueue enq: FG – serialize ACD relocate only 1 process in the cluster may do ACD relocation in a disk group
Flush Stat enq: FH – contention Serializes flush of ILM stats to disk
Flashback database log enq: FL – Flashback database log Synchronization
Flashback database log enq: FL – Flashback db command Enqueue used to synchronize Flashback Database and and deletion of flashback logs.
File Mapping enq: FM – contention Synchronizes access to global file mapping state
Oracle File System Operation enq: FO – file system operation contention Synchronizes various Oracle File system operations
File Object enq: FP – global fob contention Synchronizes various File Object(FOB) operations
Disk Group Recovery enq: FR – contention begin recovery of disk group
Disk Group Recovery enq: FR – recover the thread wait for lock domain detach
Disk Group Recovery enq: FR – use the thread indicate this ACD thread is alive
File Set / Dictionary Check enq: FS – contention Enqueue used to synchronize recovery and file operations or synchronize dictionary check
File Set / Dictionary Check enq: FS – online log operation Enqueue used to synchronize LGWR cached open online logs and online log clear/drop
Disk Group Redo Generation enq: FT – allow LGWR writes allow LGWR to generate redo in this thread
Disk Group Redo Generation enq: FT – disable LGWR writes prevent LGWR from generating redo in this thread
DBFUS enq: FU – contention This enqueue is used to serialize the capture of the DB Feature           Usage and High Water Mark Statistics
ACD Xtnt Info CIC enq: FX – issue ACD Xtnt Relocation CIC ARB relocates ACD extent
ASM Disk Header enq: HD – contention Serializes accesses to ASM SGA data structures
Queue Page enq: HP – contention Synchronizes accesses to queue pages
Hash Queue enq: HQ – contention Synchronizes the creation of new queue IDs
Direct Loader High Water Mark enq: HV – contention Lock used to broker the high water mark during parallel inserts
Segment High Water Mark enq: HW – contention Lock used to broker the high water mark during parallel inserts
Internal enq: IA – contention
IOServer Client enq: IC – IOServer clientID Gets a unique client ID
NID enq: ID – contention Lock held to prevent other processes from performing controlfile transaction while NID is running
IOServer File enq: IF – file close File Close
IOServer File enq: IF – file open File Open
Label Security enq: IL – contention Synchronizes accesses to internal label data structures
Kti blr lock enq: IM – contention for blr Serializes block recovery for IMU txn
PDB Instance State enq: IP – open/close PDB Enqueue used to synchronize instance state changes for PDBs
Instance Recovery enq: IR – arbitrate instance recovery arbitrate parallel instance recovery
Instance Recovery enq: IR – contention Synchronizes instance recovery
Instance Recovery enq: IR – contention2 Synchronizes parallel instance recovery and shutdown immediate
Instance Recovery enq: IR – global serialization serialize instance recovery globally
Instance Recovery enq: IR – local serialization serialize instance recovery with local dismount
Instance State enq: IS – contention Enqueue used to synchronize instance state changes
In-Mem Temp Table Meta Creation enq: IT – contention Synchronizes accesses to a temp object’s metadata
Job Queue Date enq: JD – contention Synchronizes dates between job queue coordinator and slave processes
Materialized View enq: JI – contention Lock held during materialized view operations (like refresh, alter) to prevent concurrent operations on the same materialized view
Job Queue enq: JQ – contention Lock to prevent multiple instances from running a single job
Job Scheduler enq: JS – aq sync Scheduler evt code and AQ sync
Job Scheduler enq: JS – contention Synchronizes accesses to the job cache
Job Scheduler enq: JS – evt notify Lock got during event notification
Job Scheduler enq: JS – evtsub add Lock got when adding subscriber to event q
Job Scheduler enq: JS – evtsub drop Lock got when dropping subscriber to event q
Job Scheduler enq: JS – job recov lock Lock to recover jobs running on crashed RAC inst
Job Scheduler enq: JS – job run lock – synchronize Lock to prevent job from running elsewhere
Job Scheduler enq: JS – q mem clnup lck Lock obtained when cleaning up q memory
Job Scheduler enq: JS – queue lock Lock on internal scheduler queue
Job Scheduler enq: JS – sch locl enqs Scheduler non-global enqueues
Job Scheduler enq: JS – wdw op Lock got when doing window open/close
SQL STATEMENT QUEUE enq: JX – SQL statement queue  statement
SQL STATEMENT QUEUE enq: JX – cleanup of  queue release SQL statement resources
Scheduler Master DBRM enq: KD – determine DBRM master Determine DBRM master
KJCI Serialization Enqueue enq: KI – contention Synchronizes Cross-Instance Calls
Scheduler enq: KM – contention Synchronizes various Resource Manager operations
Multiple Object Checkpoint enq: KO – fast object checkpoint Coordinates fast object checkpoint
Kupp Process Startup enq: KP – contention Synchronizes kupp process startup
ASM Attributes Enque enq: KQ – access ASM attribute Synchronization of ASM cached attributes
Scheduler Plan enq: KT – contention Synchronizes accesses to the current Resource Manager plan
SGA Log Operation enq: MC – Securefile log Serializes log creation/destruction with log flushes
Materialized View Log DDL enq: MD – contention Lock held during materialized view log DDL statements
SGA Log-Bkt Flush enq: MF – creating swap in instance Serializes multiple processes in creating the swap space
SGA Log-Bkt Flush enq: MF – flush bkgnd periodic Serializes flushes for a SGA log in bkgnd
SGA Log-Bkt Flush enq: MF – flush client Serializes flushes for a single SGA log – client
SGA Log-Bkt Flush enq: MF – flush destroy Serializes flushes for a single SGA log – destroy
SGA Log-Bkt Flush enq: MF – flush prior error Serializes flushes for a single SGA log – error earlier
SGA Log-Bkt Flush enq: MF – flush space Serializes flushes for a single SGA log – space lack
AQ Notification Mail Host enq: MH – contention Lock used for recovery when setting Mail Host for AQ e-mail notifications
Master Key enq: MK – contention changing values in enc$
AQ Notification Mail Port enq: ML – contention Lock used for recovery when setting Mail Port for AQ e-mail notifications
LogMiner enq: MN – contention Synchronizes updates to the LogMiner dictionary and prevents multiple instances from preparing the same LogMiner session
MMON restricted session enq: MO – contention Serialize MMON operations for restricted sessions
Media Recovery enq: MR – PDB open Lock used to coordinate media recovery with other uses of PDB datafiles
Media Recovery enq: MR – adg instance recovery Synchronizes multi instance recovery cleanup with adg instance recovery
Media Recovery enq: MR – any role transition Lock used to disallow any concurrent role transition attempt
Media Recovery enq: MR – contention Lock used to coordinate media recovery with other uses of datafiles
Media Recovery enq: MR – datafile online Enqueue used to coordinate datafile offline in RAC
Media Recovery enq: MR – multi instance redo apply Lock used to disallow concurrent recovery attempt when MIRA active
Media Recovery enq: MR – standby role transition Lock used to disallow concurrent standby role transition attempt
Materialized View Refresh Log enq: MS – contention Lock held during materialized view refresh to setup MV log
Online Datafile Move enq: MV – datafile move Held during online datafile move operation or cleanup
MWIN Schedule enq: MW – contention This enqueue is used to serialize the calibration of the           manageability schedules with the Maintenance Window
ksz synch enq: MX – sync storage server info Lock held to generate a response to the storage server information request when an instance is starting up
Outline Cache enq: OC – contention Synchronizes write accesses to the outline cache
Online DDLs enq: OD – Serializing DDLs Lock to prevent concurrent online DDLs
Outline Name enq: OL – contention Synchronizes accesses to a particular outline name
Label Security Profile enq: OP – OLS Cleanup unused profiles Synchronizing access to ols$profile when deleting unused profiles
Label Security Profile enq: OP – OLS Store user entries Synchronizing access to ols$user when inserting user entries
OLAPI Histories enq: OQ – xsoq*histrecb Synchronizes access to olapi history parameter CB
OLAPI Histories enq: OQ – xsoqhiAlloc Synchronizes access to olapi history allocation
OLAPI Histories enq: OQ – xsoqhiClose Synchronizes access to olapi history closing
OLAPI Histories enq: OQ – xsoqhiFlush Synchronizes access to olapi history flushing
OLAPI Histories enq: OQ – xsoqhistrecb Synchronizes access to olapi history globals
TEXT: Generic Lock enq: OT – TEXT: Generic Lock CTX Generic Locks
Encryption Wallet enq: OW – initialization initializing the wallet context
Encryption Wallet enq: OW – termination terminate the wallet context
Privilege Capture API enq: PA – modify a privilege capture lock held for during modify a privilege capture
Privilege Capture API enq: PA – read a privilege capture lock held for during reading privilege captur status
PDB DDL Operations enq: PB – PDB Lock Enqueue used to synchronize PDB DDL operations
Property Lock enq: PD – contention Prevents others from updating the same property
Parameter enq: PE – contention Synchronizes system parameter updates
Password File enq: PF – contention Synchronizes accesses to the password file
Global Parameter enq: PG – contention Synchronizes global system parameter updates
AQ Notification Proxy enq: PH – contention Lock used for recovery when setting Proxy for AQ HTTP notifications
Remote PX Process Spawn Status enq: PI – contention Communicates remote Parallel Execution Server Process creation status
Transportable Tablespace enq: PL – contention Coordinates plug-in operation of transportable tablespaces
Queue Ptning global enq enq: PQ – QPT LB Trunc sync kwslbFreShadowShrd:LB syncronization with Truncate
Queue Ptning global enq enq: PQ – QPT Trunc kwsptChkTrncLst:Truncate
Queue Ptning global enq enq: PQ – QPT add dqpt kwsptLoadDqCache: Add DQ Partitions.
Queue Ptning global enq enq: PQ – QPT add dqpt fg kwsptMapDqPtn:Drop DQ Partitions in foreground
Queue Ptning global enq enq: PQ – QPT add qpt kwsptLoadQCache: Add Q Partitions.
Queue Ptning global enq enq: PQ – QPT add qpt fg kwsptMapQPtn: Add Q Partitions in foreground
Queue Ptning global enq enq: PQ – QPT drop dqpt kwsptLoadDqCache:Drop DQ Partitions.
Queue Ptning global enq enq: PQ – QPT drop qpt kwsptLoadQCache:Drop Q Partitions.
Process Startup enq: PR – contention Synchronizes process startup
PX Process Reservation enq: PS – contention Parallel Execution Server Process reservation and synchronization
ASM Partnership and Status Table enq: PT – contention Synchronizes access to ASM PST metadata
KSV slave startup enq: PV – syncshut Synchronizes instance shutdown_slvstart
KSV slave startup enq: PV – syncstart Synchronizes slave start_shutdown
Buffer Cache PreWarm enq: PW – flush prewarm buffers Direct Load needs to flush prewarmed buffers if DBWR 0 holds enqueue
Buffer Cache PreWarm enq: PW – perwarm status in dbw0 DBWR 0 holds enqueue indicating prewarmed buffers present in cache
AVM RTA Access enq: PY – AVM RTA access database Database RTA info access on AVM
AVM RTA Access enq: PY – AVM RTA access database 2 Database RTA info access on AVM
AVM RTA Access enq: PY – AVM RTA access instance Instance RTA info access on AVM
Real-Time ADDM Flood Control enq: RA – RT ADDM flood control Flood control in RAC. Acquired in no-wait.
ASM Rollback Recovery enq: RB – contention Serializes ASM rollback recovery operations
Result Cache: Enqueue enq: RC – Result Cache: Contention Coordinates access to a result-set
RAC Load enq: RD – RAC load  update RAC load info
Block Repair/Resilvering enq: RE – block repair contention Synchronize block repair/resilvering operations
Data Guard Broker enq: RF – Broker State Lock Captures database-wide broker state
Data Guard Broker enq: RF – DG Broker Current File ID Identifies which configuration metadata file is current
Data Guard Broker enq: RF – FSFO Observer Heartbeat Captures recent Fast-Start Failover Observer heartbeat information
Data Guard Broker enq: RF – FSFO Primary Shutdown suspended Records when FSFO Primary Shutdown is suspended
Data Guard Broker enq: RF – FSFO string buffer Makes a FSFO-related string available across RAC instances
Data Guard Broker enq: RF – RF – Database Automatic Disable Means for detecting when database is being automatically disabled
Data Guard Broker enq: RF – atomicity Ensures atomicity of log transport setup
Data Guard Broker enq: RF – new AI Synchronizes selection of the new apply instance
Data Guard Broker enq: RF – synch: DG Broker metadata Ensures r/w atomicity of DG configuration metadata
Data Guard Broker enq: RF – synchronization: aifo master Synchronizes apply instance failure detection and failover operation
Data Guard Broker enq: RF – synchronization: critical ai Synchronizes critical apply instance among primary instances
wallet_set_mkey enq: RK – set key wallet master key rekey
RAC Encryption Wallet Lock enq: RL – RAC wallet lock RAC wallet lock
Redo Log Nab Computation enq: RN – contention Coordinates nab computations of online logs during recovery
Multiple Object Reuse enq: RO – contention Coordinates flushing of multiple objects
Multiple Object Reuse enq: RO – fast object reuse Coordinates fast object reuse
Resilver / Repair enq: RP – contention Enqueue held when resilvering is needed or when datablock is repaired from mirror
Enqueue-Cross SCN enq: RQ – AQ indexed cached commit AQ indexed cached commit
Enqueue-Cross SCN enq: RQ – AQ uncached commit WM update AQ uncached commit WM update
Enqueue-Cross SCN enq: RQ – AQ uncached dequeue AQ uncached dequeue
Enqueue-Cross SCN enq: RQ – Cross export subshard Cross(export) – truncate subshard
Enqueue-Cross SCN enq: RQ – Cross import subshard Cross(import) – free shadow shard
Enqueue-Cross SCN enq: RQ – Cross update scn Cross process updating disk
Enqueue-Cross SCN enq: RQ – Dequeue update scn Dequeue updating scn
Enqueue-Cross SCN enq: RQ – Enqueue commit cached Enqueue commit rac cached
Enqueue-Cross SCN enq: RQ – Enqueue commit uncached Enqueue commit uncached
Enqueue-Cross SCN enq: RQ – Free shadow shard Free shadow – Cross(import) shard
Enqueue-Cross SCN enq: RQ – Parallel cross update scn Parallel cross(update scn) – truncate subshard
Enqueue-Cross SCN enq: RQ – Trucate subshard Truncate – Cross(export) subshard
Workload Capture and Replay enq: RR – contention Concurrent invocation of DBMS_WORKLOAD_* package API
Reclaimable Space enq: RS – file delete Lock held to prevent file from accessing during space reclaimation
Reclaimable Space enq: RS – persist alert level Lock held to make alert level persistent
Reclaimable Space enq: RS – prevent aging list update Lock held to prevent aging list update
Reclaimable Space enq: RS – prevent file delete Lock held to prevent deleting file to reclaim space
Reclaimable Space enq: RS – read alert level Lock held to read alert level
Reclaimable Space enq: RS – record reuse Lock held to prevent file from accessing while reusing circular record
Reclaimable Space enq: RS – write alert level Lock held to write alert level
Redo Thread enq: RT – contention Thread locks held by LGWR, DBW0, and RVWR to indicate mounted or open status
Redo Thread enq: RT – thread internal enable/disable Thread locks held by CKPT to synchronize thread enable and disable
Rolling Migration enq: RU – contention Serializes rolling migration operations
Rolling Migration enq: RU – waiting Results of rolling migration CIC
Materialized View Flags enq: RW – MV metadata contention Lock held by CREATE/ALTER/DROP materialized viewwhile updating materialized view flags in detail tables
ASM Extent Relocation Lock enq: RX – relocate extent Synchronizes relocating ASM extents
ASM Extent Relocation Lock enq: RX – unlock extent Synchronizes unlocking ASM extents
RFS Logminer FLC enq: RZ – add element Synchronizes access to the foreign log cache while a structure is being inserted
RFS Logminer FLC enq: RZ – remove element Synchronizes access to the foreign log cache while a structure is being removed
LogicalStandby enq: SB – logical standby metadata Synchronizes Logical Standby metadata operations
LogicalStandby enq: SB – table instantiation Synchronizes table instantiation and EDS operations
Session Migration enq: SE – contention Synchronizes transparent session migration operations
AQ Notification Sender enq: SF – contention Lock used for recovery when setting Sender for AQ e-mail notifications
OLS groups enq: SG – OLS Add Group Synchronize access to ols$groups when zlllabGroupTreeAddGroup does a read
OLS groups enq: SG – OLS Alter Group Parent Synchronizing access to ols$groups when alter group parent
OLS groups enq: SG – OLS Create Group Synchronize access to ols$groups when creating a group
OLS groups enq: SG – OLS Drop Group Synchronizing access to ols$groups when dropping a group
Active Session History Flushing enq: SH – contention Should seldom see this contention as this Enqueue is always  acquired in no-wait mode
Streams Table Instantiation enq: SI – contention Prevents multiple streams tabel instantiations
KTSJ Slave Task Cancel enq: SJ – Slave Task Cancel Serializes cancelling task executed by slave process
Shrink Segment enq: SK – contention Serialize shrink of a segment
Serialize Lock request enq: SL – escalate lock sending lock escalate to LCK0
Serialize Lock request enq: SL – get lock sending lock req to LCK0
Serialize Lock request enq: SL – get lock for undo sending lock req for undo to LCK0
Shared Object enq: SO – contention Synchronizes access to Shared Object (PL/SQL Shared Object Manager)
Spare Enqueue enq: SP – contention 1 (1) due to one-off patch
Spare Enqueue enq: SP – contention 2 (2) due to one-off patch
Spare Enqueue enq: SP – contention 3 (3) due to one-off patch
Spare Enqueue enq: SP – contention 4 (4) due to one-off patch
Sequence Cache enq: SQ – contention Lock to ensure that only one process can replenish the sequence cache
Synchronized Replication enq: SR – contention Coordinates replication / streams operations
Sort Segment enq: SS – contention Ensures that sort segments created during parallel DML operations aren’t prematurely cleaned up
Space Transaction enq: ST – contention Synchronizes space management activities in dictionary-managed tablespaces
SaveUndo Segment enq: SU – contention Serializes access to SaveUndo Segment
Suspend Writes enq: SW – contention Coordinates the ‘alter system suspend’ operation
Instance Undo enq: TA – contention Serializes operations on undo segments and undo tablespaces
SQL Tuning Base Existence Cache enq: TB – SQL Tuning Base Cache Load Synchronizes writes to the SQL Tuning Base Existence Cache
SQL Tuning Base Existence Cache enq: TB – SQL Tuning Base Cache Update Synchronizes writes to the SQL Tuning Base Existence Cache
Tablespace Checkpoint enq: TC – contention Lock held to guarantee uniqueness of a tablespace checkpoint
Tablespace Checkpoint enq: TC – contention2 Lock of setup of a unqiue tablespace checkpoint in null mode
KTF map table enqueue enq: TD – KTF dump entries KTF dumping time/scn mappings in SMON_SCN_TIME table
KTF broadcast enq: TE – KTF broadcast KTF broadcasting
Temporary File enq: TF – contention Serializes dropping of a temporary file
Threshold Chain enq: TH – metric threshold evaluation Serializes threshold in-memory chain access
Auto Task Serialization enq: TK – Auto Task Serialization Lock held by MMON to prevent other MMON spawning of Autotask Slave
Auto Task Serialization enq: TK – Auto Task Slave Lockout Serializes spawned Autotask Slaves
Log Lock enq: TL – contention Serializes threshold log table read and update
DML enq: TM – contention Synchronizes accesses to an object
Temp Object enq: TO – contention Synchronizes DDL and DML operations on a temp object
Runtime Fixed Table Purge enq: TP – contention Lock held during purge and dynamic reconfiguration of fixed tables.
Queue table enqueue enq: TQ –  sq TM contention SQ TM access to the queue table
Queue table enqueue enq: TQ – DDL contention TM access to the queue table
Queue table enqueue enq: TQ – DDL-INI contention Streams DDL on queue table
Queue table enqueue enq: TQ – INI contention TM access to the queue table
Queue table enqueue enq: TQ – INI sq contention INIT access to the queue tables
Queue table enqueue enq: TQ – TM contention TM access to the queue table
Temporary Segment enq: TS – contention Serializes accesses to temp segments
Tablespace enq: TT – contention Serializes DDL operations on tablespaces
Cross-Instance Transaction enq: TW – contention Lock held by one instance to wait for transactions on all instances to finish
Transaction enq: TX – allocate ITL entry Allocating an ITL entry in order to begin a transaction
Transaction enq: TX – contention Lock held by a transaction to allow other transactions to wait for it
Transaction enq: TX – index contention Lock held on an index during a split to prevent other operations on it
Transaction enq: TX – row lock contention Lock held on a particular row by a transaction to prevent other transactions from modifying it
User-defined enq: UL – contention Lock used by user applications
Undo Segment enq: US – contention Lock held to perform DDL on the undo segment
AQ Notification Watermark enq: WA – contention Lock used for recovery when setting Watermark for memory usage in AQ notifications
AWR Flush enq: WF – contention This enqueue is used to serialize the flushing of snapshots
Write gather local enqueue enq: WG – delete fso acquire lobid local enqueue when deleting fso
Write gather local enqueue enq: WG – lock fso acquire lobid local enqueue when locking fso
Being Written Redo Log enq: WL – Far Sync Fail Over Exclude multiple simultaneous failovers
Being Written Redo Log enq: WL – RAC-wide SGA contention Serialize access to RAC-wide SGA
Being Written Redo Log enq: WL – RFS global state contention Serialize access to RFS global state
Being Written Redo Log enq: WL – Switchover To Primary One and only one process executing switchover to primary
Being Written Redo Log enq: WL – Test access/locking Testing redo transport access/locking
Being Written Redo Log enq: WL – contention Coordinates access to redo log files and archive logs
Being Written Redo Log enq: WL – redo_db table query Synchronize query to redo_db table
Being Written Redo Log enq: WL – redo_db table update Serialize update to redo_db table
Being Written Redo Log enq: WL – redo_log table query Synchronize query to redo_log table
Being Written Redo Log enq: WL – redo_log table update Serialize update to redo_log table
Being Written Redo Log enq: WL – redo_rta table query Synchronize query to redo_rta table
Being Written Redo Log enq: WL – redo_rta table update Serialize update to redo_rta table
WLM Plan Operations enq: WM – WLM Plan activation Synchronizes new WLM Plan activation
AWR Purge enq: WP – contention This enqueue handles concurrency between purging and baselines
LNS archiving log enq: WR – contention Coordinates access to logs by Async LNS and ARCH/FG
XDB Configuration enq: XC – XDB Configuration Lock obtained when incrementing XDB configuration version number
Auto Online Exadata disks enq: 😄 – ASM disk OFFLINE Serialize OFFLINE Exadata disk operations
Auto Online Exadata disks enq: 😄 – ASM disk ONLINE Serialize ONLINE Exadata disk operations
Auto Online Exadata disks enq: 😄 – ASM disk drop/add Serialize Auto Drop/Add Exadata disk operations
AQ Notification No-Proxy enq: XH – contention Lock used for recovery when setting No Proxy Domains for AQ HTTP notifications
ASM Extent Fault Lock enq: XL – fault extent map Keep multiple processes from faulting in the same extent chunk
ASM Extent Relocation Enqueue enq: XQ – purification wait for relocation before doing block purification
ASM Extent Relocation Enqueue enq: XQ – recovery prevent relocation during _recovery_asserts checking
ASM Extent Relocation Enqueue enq: XQ – relocation wait for recovery before doing relocation
Quiesce / Force Logging enq: XR – database force logging Lock held during database force logging mode
Quiesce / Force Logging enq: XR – quiesce database Lock held during database quiesce
Internal Test enq: XY – contention Lock used for internal testing
Audit Partition enq: ZA – add std audit table partition lock held to add partition to std audit table
FGA Partition enq: ZF – add fga audit table partition lock held to add partition to fga audit table
File Group enq: ZG – contention Coordinates file group operations
Compression Analyzer enq: ZH – compression analysis Synchronizes analysis and insert into compression$, prevents multiple threads analyzing the same table during a load
Spillover Audit files enq: ZS – excl access to spill audit file lock held while writing to/renaming/deleting spillover audit file
Global Context Action enq: ZZ – update hash tables lock held for updating global context hash tables

The SQL to retrieve the enqueue waits hasn’t changed of course:

SELECT eq.eq_name "Group", ev.name "Enqueue Type", eq.req_description "Description"
FROM v$enqueue_statistics eq, v$event_name ev
WHERE eq.event#=ev.event#
ORDER BY ev.name;

12 August 2013

Oracle 12c lock types

Filed under: Oracle — Venzi @ 11:37
Tags: ,

As stated in my previous post, it’s time for me to revisit some of my posts and check the relevance for 12c. Let’s start with the one on locks. As posted in late 2010 there are 202 different lock types in Oracle 11g. It’s actually 205 in 11.2.0.3 which was not out back then. Anyway, in 12c we have now 240 but still only 4 which are caused by user interaction, aka. SQL statements.

Non of the existing 11g locks disappeared in 12c. But following got introduced in 12c:

TYPE NAME IS_USER LIVES_IN DESCRIPTION
AC Application Continuity NO (Non-) Container/CDB Synchronizes new service activation
AQ Queue Partitioning local enqueue NO (Non-) Container/CDB Synchronizes partition operations on queue table within instance
BA non durable sub bmap alloc NO (Non-) Container/CDB Synchronizes access to globl Bitmap Alloc
BC AM Container NO (Non-) Container/CDB Availability Machine Container
BI AM Contained File Identification NO (Non-) Container/CDB Availability Machine Contained File Identification
BV AM Group Rebuild/Validate NO (Non-) Container/CDB Availability Machine Container Rebuild/Validate
BZ AM Contained File Resize NO (Non-) Container/CDB Availability Machine Contained File Resize
CB CBAC Master Lock NO (Non-) Container/CDB Synchronizes accesses to the CBAC roles cached in KGL
CC Column Key NO (Non-) Container/CDB Serializes Column Encryption Key Cache
CP Pluggable Database NO (Non-) Container/CDB Coordinate pluggable database operations
FH Flush Stat NO (Non-) Container/CDB Serializes flush of ILM stats to disk
FO Oracle File System Operation NO (Non-) Container/CDB Synchronize Oracle File System Operations
HM Hang Manager Test Resource NO (Non-) Container/CDB Resource to Generate Hangs for Hang Manager Testing
IC IOServer Client NO (Non-) Container/CDB Gets a unique client ID
IF IOServer File NO (Non-) Container/CDB Synchronizes file operations
IP PDB Instance State NO (Non-) Container/CDB Enqueue used to synchronize instance state changes for PDBs
KI KJCI Serialization Enqueue NO (Non-) Container/CDB Enqueue to Serialize KJCI Cross-Instance Calls
KZ ASM:Purge Audit NO (Non-) Container/CDB Coordinate GEN0 on multiple instances
MC SGA Log Operation NO (Non-) Container/CDB Lock held during dml/ddl for a securefile log
MF SGA Log-Bkt Flush NO (Non-) Container/CDB Lock held during flushing a log bucket
MP SGA Log Partition NO (Non-) Container/CDB Lock held during partitioning an instance log
OE DLM Enqueue Owner Serialization NO (Non-) Container/CDB Enqueue to serialize access to an Omni Enqueue Owner
OP Label Security Profile NO (Non-) Container/CDB Synchronizes access to the ols$profile table
PA Privilege Capture API NO (Non-) Container/CDB Lock held for modifying or reading a privilege capture
PB PDB DDL Operations NO (Non-) Container/CDB Enqueue used to synchronize PDB DDL operations
PP GES Plug and Play Enqueue NO (Non-) Container/CDB Coordinates Adding Plug and Play Instances
PQ Queue Ptning global enq NO (Non-) Container/CDB Synchronizes partitioning operations on queue table accross instances
PY AVM RTA Access NO (Non-) Container/CDB Coordinate RTA info access on AVM
RA Real-Time ADDM Flood Control NO (Non-) Container/CDB Coordinate Flood Control in RAC
RQ Enqueue-Cross SCN NO (Non-) Container/CDB Synchronize enqueue-dequeue-cross on SCN generation
RZ RFS Logminer FLC NO (Non-) Container/CDB Synchronizes updates to logminer foreign log cache
SG OLS groups NO (Non-) Container/CDB Synchronizes access to ols$groups table
XK GES Test-Lock for KSIRESTXDV NO (Non-) Container/CDB Lock used for internal testing
XM ASM RM Master Lock NO (Non-) Container/CDB RMON which process this lock is the Master
ZS Spillover Audit files NO (Non-) Container/CDB Lock held for exclusive access on a Spillover audit file

As you can see there is a new column LIVES_IN (named cdb_id in the v$ view) which defines whether the lock resides within a CDB (or Non-CDB if you haven’t installed the container database version) or a pluggable database. As it seems all the locks currently reside within the CDB which makes sense as locks are held within the SGA structure and this structure is held at the CDB level.

Here is the new list of all locks in 12c:

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

You can retrieve the list yourself via following query:

SELECT type, name, is_user, decode(con_id, '0', '(Non-) Container/DB', 1, 'Root DB', 'Pluggable DB') lives_in, description
FROM V$LOCK_TYPE ORDER BY is_user DESC, type;

6 August 2013

Oracle 12c and the world part II

Filed under: Oracle — Venzi @ 10:09
Tags: ,

Here is a link to a great list from The Oracle Alchemist around Oracle 12c articles from the community: http://www.oraclealchemist.com/news/install-oracle-12c-12-1/

5 August 2013

Oracle 12c and the world

Filed under: Oracle — Venzi @ 11:13
Tags: ,

Oracle Database 12c is out and the blogs out there are filled with the new features and improvements in the new version. For me it’s time to revisit some of my previous blog posts and compare them with 12c. Stay tuned….

8 July 2013

Oracle 12c database is out!

Filed under: Oracle — Venzi @ 12:09
Tags: ,

After more than 5 years of development Oracle has released version 12c of its flagship product the database. With a New Features Guide of 128 pages containing 500+ new features this is one of the biggest releases ever. You can download it for Linux and Solaris on Oracle Technology Network.

Blog at WordPress.com.