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