Venzi's Tech-Blog

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
XD 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;
About these ads

2 Comments »

  1. […] 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 […]

    Pingback by Oracle 12c enqueue waits | Venzi's Tech-Blog — 19 August 2013 @ 16:29 | Reply

  2. […] Lock Types in Oracle 12c […]

    Pingback by Oracle Database 12c (12.1) Installation and New Features | DBLinks Consulting Ltée — 19 August 2013 @ 19:58 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: