Locks

Version:     1/7/2007

Author:      Anjo Kolk

 


 

1.       Enqueues and Locks Names 4

1.1.    BL, Buffer Cache Management 4

1.2.    CF, Controlfile Transaction_ 4

1.3.    CI, Cross-instance Call Invocation_ 6

1.4.    CU, Bind Enqueue_ 7

1.5.    DF, Datafile_ 8

1.6.    DL, Direct Loader Index Creation_ 8

1.7.    DM, Database Mount 9

1.8.    DR, Distributed Recovery 10

1.9.    DX, Distributed TX_ 10

1.10.      FS, File Set 10

1.11.      IN, Instance Number 11

1.12.      IR, Instance Recovery 12

1.13.      IS, Instance State_ 12

1.14.      IV, Library Cache Invalidation_ 13

1.15.      JQ, Job Queue_ 13

1.16.      KK, Redo Log “Kick”_ 14

1.17.      L[A-P], Library Cache Lock_ 14

1.18.      MM, Mount Definition_ 15

1.19.      MR, Media Recovery 15

1.20.      Q[A-Z], Row Cache_ 16

1.21.      P[A-Z], Library Cache Pin (will be N[A-Z] in 7.1.3) 16

1.22.      PF, Password File_ 17

1.23.      PI, Parallel Slaves 17

1.24.      PR, Process Startup_ 18

1.25.      PS, Parallel Slave Synchronization_ 18

1.26.      RT, Redo Thread_ 19

1.27.      SC, System Commit Number 19

1.28.      SM, SMON_ 20

1.29.      SN, Sequence Number 21

1.30.      SQ, Sequence Number Enqueue_ 21

1.31.      SR, Synchronized Replication_ 21

1.32.      SS, Sort Segment 22

1.33.      ST, Space Management Transaction_ 22

1.34.      SV, Sequence Number Value_ 23

1.35.      TA, Transaction Recovery 23

1.36.      TM, DML Enqueue_ 24

1.37.      TS, Temporary Segment (also TableSpace) 24

1.38.      TT, Temporary Table_ 25

1.39.      TX, Transaction_ 25

1.40.      UL, User-defined Locks 26

1.41.      UN, User Name_ 26

1.42.      US, Undo segment, Serialization_ 27

1.43.      WL, Being written Redo Log_ 28

1.44.      XA, Instance Attribute Lock_ 28

1.45.      XI, Instance Registration Lock_ 28

 

 

1.               Enqueues and Locks Names

1.1.        BL, Buffer Cache Management

 

How Many Resources:

The total number of BL resources (or PCM resources) is determined by the following formula:

up to 7.3.1:

prime(gc_db_locks) + 3 + prime(gc_segments * 1.5) + prime(gc_segments * 1.5 * 5) +

prime(gc_tablespaces) + prime(gc_save_rollback_locks) + (gc_rollback_segments *

(gc_rollback_locks + 1))

after 7.3.1:

prime(gc_db_locks) + 3 + prime(gc_segments) + prime(gc_freelist_group) +

prime(gc_tablespaces) + prime(gc_save_rollback_locks) + (gc_rollback_segments *

(gc_rollback_locks + 1)) + gc_releasable_locks

How Many Locks:

The number of locks is determined by the number of instances running. For each instance

running there will be one lock allocated for each BL resource.

How Many Users:

All processes, indirectly through LCK* processes

Who Uses:

LCK* processes

When and How Used:

Page 60

Description of Oracle Wait Events and Enqueues 03/21/96 Page 60

When accessing database blocks. All acquired in NULL mode by each instance’s LCK*

processes when an instance is started. Converted up and down to different modes by LCK*

processes in response to requests from user foregrounds and instance backgrounds. Each lock

covers a portion of the physical database on disk, by file and block range, in accordance with the

setting of ‘gc_file_to_locks’.

Id1, Id2 Combination:

Lock Element Number, Block Class

Lock Value Block:

Thread number of last change and SCN.

Init.ora Parameters:

gc_db_locks, gc_segments, gc_tablespaces, gc_save_rollback_locks, gc_rollback_segments,

gc_rollback_locks.

Scope:

Global Lock.

Deadlock Sensitive:

No.

Operation:

Asynchronous.

1.2.            CF, Controlfile Transaction

How Many Resources:

1 to serialize controlfile transactions, 1 to serialize reads and writes from shared information

portion of controlfile, which is a special global notepad area of the controlfile.

How Many Locks:

1 Lock for each process that tries to perform a Control File operation.

How Many Users:

(depends)

Who Uses:

Any foreground or background doing a controlfile transaction

When Used:

- switching logfiles, held exclusive.

- updating checkpoint information for datafiles, held exclusive.

- opening a logfile for redo reading during recovery, held shared.

- getting information to perform archiving, held shared.

- performing crash recovery, held exclusive.

- performing instance recovery, held exclusive.

- performing media recovery, held exclusive.

- dumping logfile history, held shared.

- updating archiving information, then exclusive.

- creating a database, held exclusive.

- mounting a database, held shared.

- closing a database, held shared.

- adding a logfile or logfile member, held exclusive.

Page 61

Description of Oracle Wait Events and Enqueues 03/21/96 Page 61

- dropping a logfile or logfile member, held exclusive.

- checking information about logfile group members, held shared.

- adding a new datafile, held exclusive.

- dropping a datafile, held exclusive.

- re-creating (as empty) an existing database file, held shared to find the file, held exclusive to

perform the update to the file entry in the controlfile when the zeroing task is complete.

- identifying or re-identifying database files, held shared.

- setting a set of datafiles OFFLINE, held exclusive twice in a two step process.

- setting a set of datafiles ONLINE, held exclusive, and then again when the transaction

subsystem completes applying save undo to the on-lined files, a two-step process.

- marking a set of datafiles READONLY, held exclusive twice in a two-step process.

- marking a set of READONLY datafiles READWRITE, held exclusive twice in a two-step

process.

- formatting a new controlfile, i.e. CREATE CONTROLFILE, held exclusive.

- beginning a hot backup, held exclusive.

- ending a hot backup, held exclusive.

- checking to see, after a crash, whether datafiles are in hot backup mode, held shared, then

exclusive.

- executing a ALTER DATABASE BACKUP CONTROLFILE TO TRACE, held shared.

- opening the database (controlfile), held exclusive by first instance to open, shared by

subsequent instances.

- renaming datafiles or logfiles, held exclusive.

- marking a controlfile as valid and mountable, held exclusive.

- handling an error encountered in the controlfile, held exclusive.

- validating data dictionary entries against matching controlfile records for file entries, held

exclusive.

- updating controlfile format after a software upgrade, held exclusive.

- scanning for log file info by log sequence #, held shared.

- finding the highest-in-use entry of a particular controlfile record type, held shared.

- getting information about the number of log entries and their lowest/highest sequence number

and log file numbers, held shared.

- looking up a controlfile record matching a given filename, held shared.

- retrieving or updating a controlfile record, held shared for retrieval, exclusive for update.

- making a backup controlfile, held exclusive for the duration of the copy.

- dumping the contents of the controlfile during debugging, held shared.

- dumping contents of a current redo logfile during debugging, held shared.

- dumping contents of redo log headers, held shared.

- dumping contents of datafile headers, held shared.

- updating MAC security mode in Trusted Oracle, held exclusive.

- reading shared information from the controlfile, held exclusive until shared information is

written back updated, or done looking at it. Shared information is read and updated

infrequently and is used primarily for instances to agree on compatibility, modes of operation,

and for system commit number housecleaning in instance recovery situations.

Id1, Id2 Combinations:

TABLE 19. Id1, Id2 Combinations for CF

Resource.

Page 62

Description of Oracle Wait Events and Enqueues 03/21/96 Page 62

Id1 Id2 Meaning

0 0 Serialize Control file actions

0 1 Shared Information Access.

Lock Value Block:

Init.ora parameters:

processes

Scope:

Local and Global Enqueue.

Deadlock Sensitive:

Yes.

Operation:

Synchronous.

1.3.        CI, Cross-instance Call Invocation

How Many Resources:

44 Resources (4 per function, 11 Cross Instance Functions)

4/unique type of cross-instance call, acquired on the fly as a particular call is

requested (call,parameter,queue,return). The four locks for each call are used as a

mechanism to allow an RPC on a remote instance background process.

How Many Locks:

The number of locks depend on the number of concurrent processes that execute the Cross

Instance Call.

How Many Users:

Potentially all foreground or backgrounds

Who Uses:

All foreground and backgrounds

When Used:

Used to invoke specific actions in background processes on a specific instance or all instances.

Examples include checkpoint, log switch, shutting down, identifying or re-identifying datafiles,

etc. All in all, there are a small number (10’s) of predefined cross-instance call types.

Id1, Id2 Combination:

TABLE 20. Id1 for CI resource.

Id1 Meaning

0 Flush buffers for reuse as new class

1 LGWR checkpointing and Hot Backup

2 DBWR synchronization of SGA with control file

3 Log file add/drop/rename notification

4 Write buffer for CR read

5 Test Call

6 Invalidate KCK cache in all instances

7 Alter rollback segment optimal

Page 63

Description of Oracle Wait Events and Enqueues 03/21/96 Page 63

8 Signal Query Servers/coordinator

9 Create Remote Parallel Query Server

10 Set Global Partitions

11 Stop Disk Writes

12 Drop Sort Segments

13 Release unused space from Sort Segments

14 Instance Recovery for Parallel operation Group

15 Validate parallel slave Lock Value

16 Check Transaction State Objects

TABLE 21. Id2 for CI resource.

Id2 Meaning

1 Pass in Parameters

2 Invoke the call in background process

3 Foreground has not returned yet

4 Used to allocate the CI call

5 Used to queue up interested clients

Lock Value Block:

Init.ora Parameters:

processes

Scope:

Local and Global Enqueue.

Deadlock Sensitive:

Yes.

Operation:

Synchronous.

1.4.        CU, Bind Enqueue

How Many Resources:

1/child cursor

How Many Locks:

1/child cursor/process.

How Many Users:

processes * open_cursors.

Who Uses:

All processes.

When Used:

This keeps us from hanging on the exclusive pin if somebody else sets the bind types and gets

the shared-pin for the execute before we get the exclusive pin for bind (which would be

unnecessary at that point). Once we get the enqueue, we’ll check whether the cursor is still

Page 64

Description of Oracle Wait Events and Enqueues 03/21/96 Page 64

unbound, in which case we’ll verify that it was bound the way that we wanted it, or we’ll go

ahead and bind it ourselves.

Id1, Id2 Combination:

Child Object Handle, 0.

Lock Value Block:

Not Used.

Init.ora Parameters:

max_open_cursors

Scope:

Local Enqueue.

Deadlock Sensitive:

Yes.

Operation:

Synchronous.

1.5.        DF, Datafile

How Many Resources:

1/ on-line file

How Many Locks:

1 for each instance.

How Many Users:

1/ instance.

Who Uses:

DBWR’s

When Used:

Obtained shared by DBWR in every instance as the instance comes up or upon request of a reidentify

request. It is held shared for the life of the instance, and is used to notice changes like a

file being off-lined on another instance. It is also held exclusive when bringing a file on-line.

Id1, Id2 Combination:

Always 0, File Number.

Lock Value Block:

Not Used.

Init.ora Parameters:

db_files

Scope:

Global Lock.

Deadlock Sensitive:

No.

Operation:

Synchronous.

Page 65

Description of Oracle Wait Events and Enqueues 03/21/96 Page 65

1.6.        DL, Direct Loader Index Creation

How Many Resources:

1/ table being direct loaded

How Many Locks:

1 for each sqlldr process loading the table.

How Many Users:

1/ direct loader / object

Who Uses:

SQL*Loader in Direct-path mode

When Used:

When initializing to do a direct load of a table, held shared by every direct loader session

loading that object for the duration of the load. Acquired and held shared exclusive when

creating an index on the table.

Id1, Id2 Combination:

Object Number, Always 0.

Lock Value Block:

Not Used.

Init.ora Parameters:

processes.

Scope:

Local and Global Enqueue.

Deadlock Sensitive:

Yes.

Operation:

Synchronous.

1.7.        DM, Database Mount

How Many Resources:

1 if mounted EXCLUSIVE, 5 if mounted SHARED

How Many Locks:

1 Lock per instance.

How Many Users:

Any processes trying to mount/open a database

Who Uses:

Any processes trying to mount/open a database

When Used:

When mounting or opening a database, if the database is being mounted exclusive, then one lock

is acquired to ensure no other instances can successfully mount it; if the database is being

mounted shared, all five locks are used to ensure that all processes attempting to mount or open

on all instances have a consistent view of the mount or open state of the database.

Id1, Id2 Combination:

TABLE 22. Id1, Id2 Combination for DM Lock.

Page 66

Description of Oracle Wait Events and Enqueues 03/21/96 Page 66

Id1 Id2 Meaning

0 0 Mount type. This is used to determine if the DB may be mounted

shared or exclusive. It is always nowait. Only acquired by

DBWR.

0 1 Mount gate. This is used to serialize checking for the first

instance to mount. It is only acquired by foregrounds attempting

to mount the database.

0 2 Hold mount. This is held to indicate that the db is mounted by

some instance some where. Its lockvalue contains enough

information to validate that the correct controlfiles are opened.

The first foreground gets it shared. After the database is

successfully mounted, the DBWR holds this lock shared until the

DB is dismounted.

1 0 Open flag. This is used to verify that no other instance has the

database open or is in process of opening it. It is similar to the

hold open lock and is acquired and released at the same time.

This is a local enqueue rather then an instance lock. This is

separate of the hold open lock so that testing does not interfere

with deciding if this is the first instance to open. If one instance

was getting the hold lock just to see if the db was open any

where, another instance might think the database was already

opened.

1 1 Open gate. This functions identically to the mount gate except

that it is used for database open.

1 2 Hold open. This functions identically to hold mount except that it

is used for database open, and its lock value is not used.

Lock Value Block:

No.

Init.ora Parameters:

_db_no_mount_lock.

Scope:

Local and Global Enqueue (but also Global Lock).

Deadlock Sensitive:

Yes for the Local and Global Enqueue, no for the Global Lock.

Operation:

Synchronous.

1.8.        DR, Distributed Recovery

How Many Resources:

1

How Many Locks:

1 per RECO process that tries to do Distributed Recovery.

How Many Users:

1/ instance

Who Uses:

Page 67

Description of Oracle Wait Events and Enqueues 03/21/96 Page 67

RECO’s

When Used:

Used to ensure that only one RECO is doing distributed transaction recovery actions at a time.

Id1, Id2 Combination:

Always 0, Always 0

Lock Value Block:

Not Used.

Init.ora Parameters:

distributed_transactions. (Distributed Database Option)

Scope:

Global Lock.

Deadlock Sensitive:

No.

Operation:

Synchronous.

1.9.        DX, Distributed TX

How Many Resources:

1

How Many Locks:

1 per process that tries to do a distributed transaction.

How Many Users:

1/ instance

Who Uses:

RECO’s

When Used:

Used to ensure that only one process is using a distributed transaction slot at a time.

Id1, Id2 Combination:

Slot Number (of distributed transaction table), Always 0

Lock Value Block:

Not Used.

Init.ora Parameters:

distributed_transactions. (Distributed Database Option)

Scope:

Local Enqueue.

Deadlock Sensitive:

Yes.

Operation:

Synchronous.

1.10.   FS, File Set

Page 68

Description of Oracle Wait Events and Enqueues 03/21/96 Page 68

How Many Resources:

2

How Many Locks:

Depends of the number of concurrent processes that tries to do these operations.

How Many Users:

Used by processes performing actions that require that the files that make

up the database do not change the set of files it may be performing the action on.

Who Uses:

Used by processes performing actions that require that the files that make up the database do not

change the set of files it may be performing the action on.

When Used:

The first lock is used to hold the set consistent for actions like dropping or renaming a datafile.

The second is used to when validating controlfile information regarding files with information

stored in the data dictionary.

Id1, Id2 Combination:

TABLE 23. Id1, Id2 Combination for FS Lock.

Id1 Id2 Meaning

0 0 Take offline

0 1 Do dictionary check.

Lock Value Block:

Not Used.

Init.ora Parameters:

None.

Scope:

Local and Global Enqueue.

Deadlock Sensitive:

Yes.

Operation:

Synchronous.

1.11.   IN, Instance Number

How Many Resources:

1/ active instance

How Many Locks:

1/active instance

How Many Users:

1/ instance

Who Uses:

DBWR’s

When Used:

Used to either obtain or ensure that the instance_number parameter used for freelist

management, is unique for every instance accessing a database.

Id1, Id2 Combination:

Page 69

Description of Oracle Wait Events and Enqueues 03/21/96 Page 69

Always 0, Instance Number.

Lock Value Block:

Not Used.

Init.ora Parameters:

instance_number

Scope:

Global Lock.

Deadlock Sensitive:

No.

Operation:

Synchronous.

1.12.   IR, Instance Recovery

How Many Resources:

1/database

How Many Locks:

at most 1/instance

How Many Users:

at most 1/ instance

Who Uses:

SMON’s and processes attempting to open the database

When Used:

Used to ensure that only one SMON process or ALTER DATABASE OPEN is performing

instance recovery at any given time.

Id1,Id2 Combination:

TABLE 24.

Id1 Id2 Meaning Scope

0 0 To serialize instance recoverers. Local and Global

Enqueue

1 0 Used to verify if writes are allowed in the database. Global Lock

Lock Value Block:

Not Used.

Init.ora Parameters:

None.

Scope:

Local and Global Enqueue and Global Lock.

Deadlock Sensitive:

Yes for the Local and Global Enqueue only.

Operation:

Synchronous.

Page 70

Description of Oracle Wait Events and Enqueues 03/21/96 Page 70

1.13.   IS, Instance State

How Many Resources:

3

How Many Locks:

Could range from 0 to the number of processes in an instance.

How Many Users:

All processes that want to mount/dismount or open/close a database. Also held by users

who want to keep the instance in its current state.

Who Uses:

All processes that want to mount/dismount or open/close a database. Also held by users

who want to keep the instance in its current state.

When Used:

Processes that wish to modify the instance state acquire the locks in exclusive mode. Processes

that want to hold the instance in a particular mode acquire them shared.

Id1, Id2 Combination:

TABLE 25. Id1, Id2 Combination for IS Lock.

Id1 Id2 Meaning

0 0 Background Instance State Lock

0 1 Changing mount or open state of the database state on this

instance.

0 3 Modify Cache State.

Lock Value Block:

Not Used.

Init.ora Parameters:

None.

Scope:

Local Enqueue.

Deadlock Sensitive:

Yes.

Operation:

Synchronous.

1.14.   IV, Library Cache Invalidation

How Many Resources:

1 / valid and existent database object that is currently cached in the library cache (valid and

existent table, view, procedure, function, package, package body, trigger, index, cluster,

synonym); cursors (SQL and PL/SQL), pipes, invalid or non-existent objects, and any other

transient objects do not use this lock

How Many Locks:

1/per instance/object.

How Many Users:

All processes

Page 71

Description of Oracle Wait Events and Enqueues 03/21/96 Page 71

Who Uses:

LCK? and the process which wants to invalidate objects cached across all instances

When Used:

When a valid and existent database object is brought into the library cache, LCK? is asked to

acquire the lock in the correct mode (always S). It is held until the object becomes invalid or

non-existent or is aged out of the library cache. This lock is used to invalidate objects cached

across all instances. The process that wants to invalidate an object simply acquires the lock in X

mode on the object causing all instances to invalidate their cached objects responding to BAST’s

and release their IV locks on their objects; then, the invalidating process immediately releases

the X lock.

Id1, Id2 Combination:

Object Number, Timestamp.

Lock Value Block:

Not Used.

Init.ora Parameters:

None.

Scope:

Global Lock.

Deadlock Sensitive:

No.

Operation:

Synchronous.

1.15.   JQ, Job Queue

How Many Resources:

1 / job

How Many Locks:

1/job.

How Many Users:

All SNP process

Who Uses:

SNP? processes.

When Used:

To synchronize access to the job.

Id1, Id2 Combination:

0, job number.

Lock Value Block:

Not Used.

Init.ora Parameters:

job_queue_processes, snapshot_refresh_processes.

Scope:

Local and Global Enqueue.

Deadlock Sensitive:

Page 72

Description of Oracle Wait Events and Enqueues 03/21/96 Page 72

Yes.

Operation:

Synchronous.

1.16.   KK, Redo Log “Kick”

How Many Resources:

1/ redo thread, i.e. 1/instance in 7.x.

How Many Locks:

1/instance.

How Many Users:

1/ instance

Who Uses:

LGWR’s

When Used:

Used to keep on-line redo logs archiving off of idle instances, while other active instances

generate redo and archive on-line logs. The intent is to keeps archive streams from all

participating instances close to each other in time, so that a set of archived logs for all redo

streams/instances can easily be identified and managed for backup or recovery. Also used in

executing the ALTER SYSTEM ARCHIVE LOG CURRENT command, which is used to cause

all instances to archive their current logs.

Id1, Id2 Combination :

Always 0, Thread Number.

Lock Value Block:

Not Used.

Init.ora Parameters:

thread

Scope:

Global Lock.

Deadlock Sensitive:

No.

Operation:

Synchronous.

1.17.   L[A-P], Library Cache Lock

How Many Resources:

1 / database object referenced during parsing or compiling of SQL or PL/SQL statements (table,

view, procedure, function, package, package body, trigger, index, cluster, synonym); released at

the end of parse or compilation; cursors (SQL and PL/SQL areas), pipes and any other transient

objects do not use this lock.

How Many Locks:

How Many Users:

Page 73

Description of Oracle Wait Events and Enqueues 03/21/96 Page 73

All processes

Who Uses:

All processes

When Used:

When a database object (table, view, procedure, function, package, package body, trigger, index,

cluster, synonym) is referenced during parsing or compiling of a SQL (DML/DDL) or PL/SQL

statement, the process parsing or compiling the statement acquires the lock in the correct mode.

It is held until the parse or compilation completes (for the duration of the parse call). In V6,

these locks are known as parse or DDL locks except that in V6 these locks are held for the

duration of the cursor (i.e., until the cursor is closed); in Oracle7, these locks are held only

during the parse call.

Id1, Id2 Combination:

Lock Value Block:

Not Used.

Init.ora Parameters:

shared_pool

Scope:

Global Lock.

Deadlock Sensitive:

Yes.

Operation:

Synchronous.

1.18.   MM, Mount Definition

How Many Resources:

1/ read-only database being mounted to the read-write database

How Many Locks:

How Many Users:

Any process trying to mount a read-only database under Trusted or any process asking

for library cache cleanout of remote objects.

Who Uses:

Any process trying to mount a read-only database under Trusted or any process asking for

library cache cleanout of remote objects.

When Used:

Only used by Trusted Oracle7 currently.

Id1, Id2 Combination:

Lock Value Block:

Init.ora Parameters:

Scope:

Page 74

Description of Oracle Wait Events and Enqueues 03/21/96 Page 74

Deadlock Sensitive:

No.

Operation:

Synchronous.

1.19.   MR, Media Recovery

How Many Resources:

1+1/ file.

How Many Locks:

1 for each DBWR in each instance.

How Many Users:

Any proc. doing media recovery or ALTER DATABASE OPEN RESETLOGS

Who Uses:

Any proc. doing media recovery or ALTER DATABASE OPEN RESETLOGS

When Used:

Held shared by any process performing media recovery to prevent any other process from

performing an ALTER DATABASE OPEN RESETLOGS. The file access locks are held shared

by on-line users of a particular datafile to prevent recovery actions from taking place. They are

held exclusive by processes performing recovery actions on that file.

Id1, Id2 Combination:

TABLE 26. Id1, Id2 Combinations for MR Lock

Id1 Id2 Meaning

0 0 Media Recovery Enqueue

X - doing Reset Logs

S - doing Recovery

file# 0 File Access Enqueue, used to coordinate recovery.

Lock Value Block:

Not Used.

Init.ora Parameters:

db_files

Scope:

Local and Global Enqueue

Deadlock Sensitive:

Yes.

Operation:

Synchronous.

1.20.   Q[A-Z], Row Cache

How Many Resources:

Page 75

Description of Oracle Wait Events and Enqueues 03/21/96 Page 75

1/ row cache entry of any type (corresponds to dc_* params) (ts$, fet$, seg$, undo$,

uet$, tsq$, file$, user$, obj$, tables, views, clusters, synonyms, user names, object ids,

constraints, sequence cache entries, profiles, database links, histograms).

How Many Locks:

1 per instance.

How Many Users:

All processes

Who Uses:

All processes

When Used:

Used to keep row cache entries coherent across instances. Row cache entries are caches of

recently used data dictionary information. The second part of the type [A-Z] is determined by

the type of the parent cache (0=A, 1=B ... 25=Z)

Id1, Id2 Combinations:

Both values are being hashed from ??.

Lock Value Block:

Init.ora Parameters:

shared_pool

Scope:

Global Lock.

Deadlock Sensitive:

No.

Operation:

Synchronous.

1.21.   P[A-Z], Library Cache Pin (will be N[A-Z] in 7.1.3)

How Many Resources:

1 / database object that is currently cached in the library cache (table, view, procedure, function,

package, package body, trigger, index, cluster, synonym); in the library cache, a database object

is cached in 2 parts: “handle” and “object”; only when the “object” part is cached, this lock is

held; cursors (SQL and PL/SQL areas), pipes and any other transient objects do not use this lock.

How Many Locks:

1 per process pinning the cursor.

How Many Users:

All processes

Who Uses:

Foregrounds.

When Used:

Used when pinning “object” parts of database objects (see above) in an instance’s library cache.

Held until the “object” part is flushed or aged out (e.g., as the result of another object needing

cache space, and the object to be flushed is not pinned or in use).

Id1, Id2 Combinations:

Page 76

Description of Oracle Wait Events and Enqueues 03/21/96 Page 76

Lock Value Block:

Init.ora Parameters:

cursor_space_for_time.

Scope:

Global Lock.

Deadlock Sensitive:

No.

Operation:

Synchronous.

1.22.   PF, Password File

How Many Resources:

1

How Many Locks:

How Many Users:

All processes either accessing or modifying the password file.

Who Uses:

All processes either accessing or modifying the password file.

When Used:

Acquired exclusive to read or update the password file.

Id1, Id2 Combination:

Lock Value Block:

Init.ora Parameters:

Scope:

a

Deadlock Sensitive:

No.

Operation:

Synchronous.

1.23.   PI, Parallel Slaves

How Many Resources:

1/ parallel query, index, recovery process group

How Many Locks:

How Many Users:

Page 77

Description of Oracle Wait Events and Enqueues 03/21/96 Page 77

All processes trying to add parallel slaves to a slave server group

Who Uses:

All processes trying to add parallel slaves to a slave server group

When Used:

To synchronize the creation of new parallel slave servers and their addition to a parallel slave

group on behalf of a parallel query, a parallel index creation, or a parallel recovery session.

Id1, Id2 Combinations:

Lock Value Block:

Init.ora Parameters:

Scope:

Deadlock Sensitive:

No.

1.24.   PR, Process Startup

How Many Resources:

1

How Many Locks:

1/process

How Many Users:

Any process starting up a background process or an MTS server.

Who Uses:

Any process starting up a background process or an MTS server.

When Used:

Acquired exclusive to serialize creation of background or MTS server processes.

Id1, Id2 Combinations:

0, 0.

Lock Value Block:

Not used.

Init.ora Parameters:

processes

Scope:

Local.

Deadlock Sensitive:

No.

Operation:

Synchronous.

Page 78

Description of Oracle Wait Events and Enqueues 03/21/96 Page 78

1.25.   PS, Parallel Slave Synchronization

How Many Resources:

1/ parallel slave server process

How Many Locks:

1/process

How Many Users:

All processes working on behalf of a parallel query, index creation, or recovery action.

Who Uses:

All processes working on behalf of a parallel query, index creation, or recovery action.

When Used:

Used to control execution flow of parallel slaves for parallel query, parallel index creation, and

parallel recovery.

Id1, Id2 Combination:

Instance Id, Server Id

Instance Id << 16, Server Id

Lock Value Block:

process queue at remote site (pointer), flag (1 byte)(0x01=descriptor initialized, 0x02=Query

Coordinator Queue, 0x80=Enqueue test bit), server id (1 byte), instance id (2 bytes), OS specific

descriptor (8 bytes or less).

Init.ora Parameters:

min_parallel_servers, max_parallel_servers.

Scope:

Local, Global.

Deadlock Sensitive:

No.

Operation:

Synchronous.

1.26.   RT, Redo Thread

How Many Resources:

Up to 12, one for LGWR, one for DBWR, and one for each configured LCK process

[LCK0-LCK9]

How Many Locks:

How Many Users:

Up to 3-12/instance

Who Uses:

Each of the set above, and by processes testing to see if instance has failed.

When Used:

Used to detect the death of an instance after a BL lock is found to be invalid. Each is acquired

exclusive by the associated process of each instance. If they can ever all be granted for a given

set of processes associated with a particular instance, then instance failure can be inferred.

Page 79

Description of Oracle Wait Events and Enqueues 03/21/96 Page 79

Id1, Id2 Combination:

TABLE 27.

Id1 Id2 Meaning

Thread 0 Mount Enqueue for that Thread

Thread 1 LGWR

Thread 2 DBWR

Thread 3 LCK0

Thread 4 LCK1

Thread 5 LCK2

Thread 6 LCK3

Thread 7 LCK4

Thread 8 LCK5

Thread 9 LCK6

Thread 10 LCK7

Thread 11 LCK8

Thread 12 LCK9

Lock Value Block:

Not Used.

Init.ora Parameters:

gc_lck_procs

Scope:

Local (Mount Queue only), Global

Deadlock Sensitive:

No.

Operation:

Synchronous.

1.27.   SC, System Commit Number

How Many Resources:

1

How Many Locks:

1/process

How Many Users:

All processes

Who Uses:

All processes

When Used:

The System Commit Number (SCN) is used to serialize time within a single instance, and across

all instances. This lock resource caches the current value of the SCN, the value is incremented in

response to many database event, but most notably COMMIT WORK. Access to the SCN lock

value to get and store the SCN is batched on most cluster implementations, so that every process

that needs a new SCN gets one and stores a new value back on one instance, before the SCN

Page 80

Description of Oracle Wait Events and Enqueues 03/21/96 Page 80

lock is released so that it may be granted to another instance. Processes get the SC lock once and

then use conversion operations to manipulate the lock value.

Id1, Id2 Combination:

TABLE 28.

Id1 Id2 Meaning

0 0 SCN Global lock

0 2 Local SCN batching enqueue

Lock Value Block:

System Commit Number, System Commit Number.

Init.ora Parameters:

processes

Scope:

Local (batching enqueue only), Global.

Deadlock Sensitive:

No.

Operation:

Synchronous.

1.28.   SM, SMON

How Many Resources:

1

How Many Locks:

1 for each instance.

How Many Users:

1/ instance

Who Uses:

SMON’s

When Used:

Used whenever an SMON wakes up to see if there is work to do. Acquired when SMON’s are

started in NULL mode, and converted to exclusive and back when checking for work to do,

currently every five minutes.

Id1, Id2 Combinations:

Always 0, Always 0.

Lock Value Block:

Timestamp (4 bytes) in seconds of when SMON was run the last time.

Init.ora Parameters:

No.

Scope:

Global.

Deadlock Sensitive:

No.

Operation:

Synchronous.

Page 81

Description of Oracle Wait Events and Enqueues 03/21/96 Page 81

1.29.   SN, Sequence Number

How Many Resources:

1/ cached sequence

How Many Locks:

How Many Users:

All processes using sequences.

Who Uses:

All processes using sequences.

When Used:

When using sequence number generators.

Id1, Id2 Combinations:

Always 0, Object Number.

Lock Value Block:

Init.ora Parameters:

sequence_cached_entries

Scope:

Deadlock Sensitive:

No.

Operation:

Synchronous.

1.30.   SQ, Sequence Number Enqueue

How Many Resources:

1/ cached sequence

How Many Locks:

How Many Users:

All processes using sequences.

Who Uses:

All processes using sequences.

When Used:

When using sequence number generators.

Id1, Id2 Combinations:

Always 0, Object Number.

Lock Value Block:

Init.ora Parameters:

Page 82

Description of Oracle Wait Events and Enqueues 03/21/96 Page 82

Scope:

Deadlock Sensitive:

No.

Operation:

Synchronous.

1.31.   SR, Synchronized Replication

How Many Resources:

1/ cached sequence

How Many Locks:

How Many Users:

All processes using sequences.

Who Uses:

All processes using sequences.

When Used:

When using sequence number generators.

Id1, Id2 Combinations:

Always 0, Always 0.

Lock Value Block:

Init.ora Parameters:

Scope:

Deadlock Sensitive:

No.

Operation:

Synchronous.

1.32.   SS, Sort Segment

How Many Resources:

2/ tablespace

How Many Locks:

How Many Users:

All processes using sort segments.

Who Uses:

All processes using sert segments.

Page 83

Description of Oracle Wait Events and Enqueues 03/21/96 Page 83

When Used:

When sorting.

Id1, Id2 Combinations:.

TABLE 29.

Id1 Id2 Meaning

tablespace id1 Local enqueue, to notify SMON to request some

action for tablespace identified by tablespace id.

tablespace id2 Global enqueue, to release space for releasing space

in tablespace identified by tablespace id.

Lock Value Block:

Init.ora Parameters:

Scope:

Enqueue (Local, Global)

Deadlock Sensitive:

Yes.

Operation:

Synchronous.

1.33.   ST, Space Management Transaction

How Many Resources:

1

How Many Locks:

How Many Users:

Any process allocating, freeing, or coalescing free space.

Who Uses:

Any process allocating, freeing, or coalescing free space.

When Used:

Used to serialize space transactions across all instances, which are executed within a transaction

when space requires allocation or de-allocation. Also acquired by SMON or foreground

processes when two or more physically adjacent free extents are coalesced back into one extent.

Id1, Id2 Combinations:

Always 0, Always 0.

Lock Value Block:

Init.ora Parameters:

Scope:

Local, Global.

Deadlock Sensitive:

Page 84

Description of Oracle Wait Events and Enqueues 03/21/96 Page 84

No.

Operation:

Synchronous.

1.34.   SV, Sequence Number Value

How Many Resources:

1/ cached sequence

How Many Locks:

How Many Users:

All processes using sequences.

Who Uses:

All processes using sequences.

When Used:

When using sequence number generators.

Id1, Id2 Combinations:

Always 0, Object Number.

Lock Value Block:

Init.ora Parameters:

Scope:

Local, Global.

Deadlock Sensitive:

No.

Operation:

Synchronous.

1.35.   TA, Transaction Recovery

How Many Resources:

2 + 2/ in-use, non-SYSTEM rollback segment

How Many Users:

Any process attempting to do transaction recovery.

Who Uses:

LGWR’s, SMON’s and any process doing transaction recovery.

When Used:

An SMON acquires locks exclusive to do instance recovery. LGWR’s hold the locks shared

and/or exclusive for in-use/on-line rollbacks segments.Held exclusive by foreground processes

when brining rollback segments on-line.

Id1, Id2 Combination:

TABLE 30. Id1, Id2 Combinations for TA lock.

Id1 Id2 Meaning

Page 85

Description of Oracle Wait Events and Enqueues 03/21/96 Page 85

1 undo seg# Instance lock on undo segment

2 0 Instance lock on instance existence

3 undo seg# instance lock on undo segment

4 0 mutual-exclusive lock starting up

5 0 transaction enqueue

Lock Value Block:

Not Used.

Init.ora Parameters:

gc_rollback_segments, max_rollback_segments.

Scope:

Global.

Deadlock Sensitive:

No.

Operation:

Synchronous.

1.36.   TM, DML Enqueue

How Many Resources:

1/database + 1/ table referenced.

How many Locks:

1/process accessing a table in (update, insert,delete).

How Many Users:

1/ instance + n/ table for n concurrent users of a table

Who Uses:

All processes when referencing tables *and* dml_locks > 0

When Used:

The instance lock is used when an instance attempts to mount the database to ensure that all

participating instances either have dml_locks = 0 or dml_locks != 0, if not ORA-61 is returned

and the mount attempt fails. The per table locks are acquired during the execution of a

transaction when referencing a table with a DML statement so that the object is not dropped or

altered during the execution of the transaction, if and only if the dml_locks parameter is nonzero.

Id1, Id2 Combinations:

TABLE 31.

Id1 Id2 Meaning

0 0 Check if the instances have dml_locks = 0

0 object# Global lock on table identified by object#

Lock Value Block:

Not Used.

Init.ora Parameters:

dml_locks

Scope:

Page 86

Description of Oracle Wait Events and Enqueues 03/21/96 Page 86

Local, Global.

Deadlock Sensitive:

No.

Operation:

Synchronous.

1.37.   TS, Temporary Segment (also TableSpace)

How Many Resources:

2/ temporary segment (e.g. for sorts)

1/ tablespace being dropped or having a rollback segment created in it.

How Many Locks:

1 for each process doing one of operations below.

How Many Users:

All processes using temp space, DROP TABLESPACE, or CREATE ROLLBACK SEGMENT

Who Uses:

All processes using temp space, DROP TABLESPACE, or CREATE ROLLBACK

SEGMENT

When Used:

The two temporary segment locks are used for two different purposes on each temp segment.

The first use is to serialize the “high water mark” or where the highest allocated point of the

segment is. The second is used to serialize creation, use, and deletion of a temp segment. It is

acquired by LCK?. The tablespace enqueue is acquired either when dropping that tablespace or

when creating a rollback segment in it. The purpose is to avoid deadlocks that can occur on

resource in the row cache (dictionary cache).

Id1, Id2 Combinations:

TABLE 32.

Id1 Id2 Meaning

segment dba 0 Serialize access to the “high water mark”

segment dba 1 Create, use, delete temp segment

tablespace

number

2 prevent deadlock during create rollback segment and create

tablespace.

Lock Value Block:

Not Used.

Init.ora Parameters:

_bump_highwater_mark_count (7.3).

Scope:

Local, Global

Deadlock Sensitive:

No.

Operation:

Synchronous.

Page 87

Description of Oracle Wait Events and Enqueues 03/21/96 Page 87

1.38.   TT, Temporary Table

How Many Resources:

1

How Many Locks:

1 per instance.

How Many Users:

Any process starting an instance.

Who Uses:

Any process starting an instance.

When Used:

Used after recovery of an instance at instance startup to allocate and initialize private and public

rollback segments to the instance being started. Acquired exclusive for the duration of the

operation.

Id1, Id2 Combinations:

Always 0, Always 0.

Lock Value Block:

Not Used.

Init.ora Parameters:

rollback_segments, gc_rollback_segments (if running in Parallel mode).

Scope:

Global Lock.

Deadlock Sensitive:

No.

Operation:

Synchronous.

1.39.   TX, Transaction

How Many Resources:

1/ active transaction

How Many Locks:

1/transaction + 1/process waiting for a locked row by that transaction.

How Many Users:

1 + 1/ process waiting for something locked by this transaction

Who Uses:

All processes

When Used:

Acquired exclusive when a transaction initiates its first change and held until the transaction

does a COMMIT or ROLLBACK. Also acquired exclusive by SMON when doing recovery

(undo) of a transaction. Used as a queuing mechanism for processes waiting for something locks

by a transaction’s change to become available again.

Id1, Id2 Combinations:

undo segment number << 16 | slot, sequence

Page 88

Description of Oracle Wait Events and Enqueues 03/21/96 Page 88

Lock Value Block:

Not Used.

Init.ora Parameters:

transactions

Scope:

Local and Global Enqueue.

Deadlock Sensitive:

Yes.

Operation:

Synchronous.

1.40.   UL, User-defined Locks

How Many Resources:

As many as put in use by the DBMS_LOCK package.

How Many Locks:

1 per process acquiring a lock on a resource.

How Many Users:

As many as are using the DBMS_LOCK package to synchronize user applications

between processes on multiple instances.

Who Uses:

Any application process using the DBMS_LOCK package.

When Used:

In accordance with the client applications use of the DBMS_LOCK package in PL/SQL.

Id1, Id2 Combinations:

Application Dependent, Application Dependent.

Lock Value Block:

Not Used.

Init.ora Parameters:

No.

Scope:

Local and Global Enqueue.

Deadlock Sensitive:

Yes.

Operation:

Synchronous.

1.41.   UN, User Name

How Many Resources:

1/ active userid

How Many Locks:

Page 89

Description of Oracle Wait Events and Enqueues 03/21/96 Page 89

How Many Users:

1/ session using a particular userid

Who Uses:

Processes logging in to the database or doing DROP USER.

When Used:

During login, locks are acquired shared to ensure that no other process does a DROP USER of

that particular user’s information in the dictionary. Acquired exclusive during DROP USER.

Id1, Id2 Combinations:

User Number, Always 0.

Lock Value Block:

Not Used.

Init.ora Parameters:

No.

Scope:

Local and Global Enqueue.

Deadlock Sensitive:

Yes.

Operation:

Synchronous.

1.42.   US, Undo segment, Serialization

How Many Resources;

1 per rollback segment.

How Many Locks:

1 per user requesting access to a RBS.

How Many Users:

1 .. processes

Who Uses:

All foregrounds, SMON and PMON.

When Used:

To serialize DDL on a given undo segment (rollback segment). It serializes the following

operations:

- CREATE ROLLBACK SEGMENT

- DROP ROLLBACK SEGMENT

- ALTER ROLLBACK SEGMENT ONLINE

- ALTER ROLLBACK OFFLINE

- ALTER ROLLBACK SEGMENT SHRINK

- ALTER ROLLBACK SEGMENT STORAGE

- Offlining PENDING OFFLINE RBS by SMON

- SMON - abortive offline cleanup.

- STARTUP.

Id1, Id2 Combination:

Page 90

Description of Oracle Wait Events and Enqueues 03/21/96 Page 90

Undo Segment#, Always 0.

Lock Value Block:

No.

Init.ora Parameters:

gc_rollback_locks, rollback_segments.

Scope:

Local and Global Enqueue.

Deadlock Sensitive:

Yes.

Operations:

Synchronous.

1.43.   WL, Being written Redo Log

How Many Resources:

1/ redo log being archived

How many Locks:

How Many Users:

1/ instance + any foreground sessions which are archiving

Who Uses:

ARCH’s + any foreground sessions which are archiving

When Used:

Used to determine whether or not a logfile is available for archiving (it is unavailable if some

other process is already archiving it). Acquired shared to do the test, and acquired exclusive by

the process actually doing archiving.

Id1, Id2 Combinations:

Log Number, Always 0.

Lock Value Block:

Not Used.

Init.ora Parameters:

No.

Scope:

Local and Global Enqueue.

Deadlock Sensitive:

Yes.

Operation:

Synchronous.

1.44.   XA, Instance Attribute Lock

How Many Resources:

1/ registered attribute

Page 91

Description of Oracle Wait Events and Enqueues 03/21/96 Page 91

How many Locks:

1/registered attribute

How Many Users:

1/ instance + any foreground sessions which are archiving

Who Uses:

DBWR’s hold the lock. Any processes interested in the atribute reads the lock.

When Used:

Used to store attribute values. Read when a process wants to know a certain atribute of another

process.

Id1, Id2 Combinations:

.

Lock Value Block:

Init.ora Parameters:

No.

Scope:

Global.

Deadlock Sensitive:

No.

Operation:

Synchronous.

1.45.   XI, Instance Registration Lock

How Many Resources:

1/ instance + (total lock, bitmap lock(s), join lock, recover lock)

How many Locks:

How Many Users:

1/ instance + any foreground sessions which are archiving

Who Uses:

DBWR’s hold the locks. Foregrounds and backgrounds use the locks to register the instances.

When Used:

Used to assign unique instance numbers to instances. Also used to notify instances about system

reconfigurations (instances coming up or down).

Id1, Id2 Combinations:

.

Lock Value Block:

Init.ora Parameters:

No.

Scope:

Global.

Deadlock Sensitive:

Page 92

Description of Oracle Wait Events and Enqueues 03/21/96 Page 92