Guenadi N Jilevski's Oracle BLOG


Explanation of ORA-00054 Error

Explanation of ORA-00054 Error

Generally ORA-00054 is reported if a SQL statement would have blocked waiting for another user to complete some operation.

Issuing any of the following two type of SQL statements can cause ORA-54 problem.
i) SQL statement had a ‘NOWAIT’ clause so instead of blocking the statement returns this error.
ii) SQL statement was DDL operation and was blocked.

So for both DDL or SELECT .. FOR UPDATE NOWAIT can raise this error if an incompatible lock is held.

Diagnosis of the problem
A) The V$LOCK view lists the locks currently held by the Oracle Database and outstanding
requests for a lock or latch.
Let’s have a look at column of v$lock view.

SQL> desc v$lock;
Name                                      Null?    Type
—————————————– ——– ————
ADDR                                               RAW(4)
KADDR                                              RAW(4)
SID                                                NUMBER
TYPE                                               VARCHAR2(2)
ID1                                                NUMBER
ID2                                                NUMBER
LMODE                                              NUMBER
REQUEST                                            NUMBER
CTIME                                              NUMBER
BLOCK                                              NUMBER
– Where TYPE indicates type of user or system lock.
The locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks.
The user type locks are:
i) TM – DML enqueue
ii) TX – Transaction enqueue
iii)UL – User supplied

The system type locks are:

1)   BL – Buffer hash table instance
2)   NA..NZ – Library cache pin instance (A..Z = namespace)
3)   CF – Control file schema global enqueue
4)   PF – Password File
5)   CI – Cross-instance function invocation instance
6)   PI, PS – Parallel operation
7)   CU – Cursor bind
8)   PR – Process startup
9)   DF – Data file instance
10)  QA..QZ – Row cache instance (A..Z = cache)
11)  DL – Direct loader parallel index create
12)  RT – Redo thread global enqueue
13)  DM – Mount/startup db primary/secondary instance
14)  SC – System change number instance
15)  DR – Distributed recovery process
16)  SM – SMON
17)  DX – Distributed transaction entry
18)  SN – Sequence number instance
19)  FS – File set
20)  SQ – Sequence number enqueue
21)  HW – Space management operations on a specific segment
22)  SS – Sort segment
23)  IN – Instance number
24)  IR -Instance recovery serialization global enqueue
25)  SV – Sequence number value
26)  IS – Instance state
27)  TA – Generic enqueue
28)  IV – Library cache invalidation instance
29)  TS – Temporary segment enqueue (ID2=0)
30)  JQ – Job queue
31)  TS – New block allocation enqueue (ID2=1)
32)  KK – Thread kick
33)  TT – Temporary table enqueue
34)  LA .. LP – Library cache lock instance lock (A..P = namespace)
35)  UN – User name
36)  MM – Mount definition global enqueue
37)  US – Undo segment DDL
38)  MR – Media recovery
39)  WL – Being-written redo log instance
40)  ST Space transaction enqueue

– LDOME is Lock mode in which the session holds the lock.
– REQUEST is Lock mode in which the process requests the lock.
– Both LMODE and REQUEST can have 0 to 6 value which means
0 – none
1 – null (NULL)
2 – row-S (SS)
3 – row-X (SX)
4 – share (S)
5 – S/Row-X (SSX)
6 – exclusive (X)

B) To find out the blocked process issue,
SQL> select * from v$lock where request!=0;

C) If you see locks being taken out on a child table when doing deletes on the parent then indexing the foreign key column on the child table will stop happening error ORA-00054.

D) In order to find the list of locks in a database issue following query,

SQL> column lock_id1 format a10
SQL> set pagesize 120
SQL> column lock_type format a12
SQL> column mode_held format a10
SQL> column mode_requested format a10
SQL> column blocking_others format a20
SQL> column username format a10
SQL> SELECT     session_id
,  lock_type
,  mode_held
,  mode_requested
,  blocking_others
,  lock_id1
FROM       dba_lock l
WHERE      lock_type NOT IN (‘Media Recovery’, ‘Redo Thread’)

———- ———— ———- ———- ——————– ———-
159 XR           Null       None       Not Blocking         4
159 Control File Row-S (SS) None       Not Blocking         0
159 RS           Row-S (SS) None       Not Blocking         25
153 AE           Share      None       Not Blocking         99
158 Temp Segment Row-X (SX) None       Not Blocking         3
161 PW           Row-X (SX) None       Not Blocking         1
132 AE           Share      None       Not Blocking         99
156 AE           Share      None       Not Blocking         99
132 TO           Row-X (SX) None       Not Blocking         62986
127 CU           Exclusive  None       Not Blocking         640372732
127 AE           Share      None       Not Blocking         99
127 FU           Exclusive  None       Not Blocking         0
132 DML          Row-X (SX) None       Not Blocking         70833
127 DML          Row-X (SX) None       Not Blocking         5750
127 DML          Row-X (SX) None       Not Blocking         56048
153 DML          Row-X (SX) None       Not Blocking         70833
127 Transaction  Exclusive  None       Not Blocking         65550
153 Transaction  Exclusive  None       Not Blocking         589833

18 rows selected.

June 11, 2009 Posted by | oracle | Leave a comment