ORA-00055: maximum number of DML locks exceeded

ORA-00055: maximum number of DML locks exceeded

A DML lock is a lock obtained on a table that is undergoing a DML operation (insert, update, delete).
DML_LOCKS specifies the maximum number of DML locks on each table modified in a transaction.
The value should equal the grand total of locks on tables currently referenced by all users.

Error:
ORA-00055: maximum number of DML locks exceeded

Cause:
DML LOCK parameter reached its upper limit thats why we are getting the error

Solution
1. Check the utilization from the resource limit view.

set line 200 pages 200
col initial_allocation for a10
col resource_name for a13
col current_utilization for 9999999
col Max_utilization for 9999999
col limit_value for a12
select resource_name,current_utilization,max_utilization,initial_allocation,limit_value from v$resource_limit where resource_name = 'dml_locks';

RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALUE
------------- ------------------- --------------- ---------- ------------
dml_locks                       0               0       3412  UNLIMITED

2. Check the parameter and update its value of DML_LOCKS parameters.
Note: For change this parameter we need to reboot the database.

-- Check value
SQL> show parameter dml_locks

NAME          TYPE      VALUE
------------- --------- ---------
dml_locks     integer   3412

--Change the value of DML_LOCKS parameter
SQL> alter system set dml_locks = 5000 scope=spfile;
System altered.

--Reboot the database
shutdown immediate
startup

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.