Saturday, April 19, 2008

ORA-00055: maximum number of DML locks exceeded

ORA-00055: maximum number of DML locks exceeded

Cause: Ran out of DML lock state objects.

Action: Increase the value of the DML_LOCKS initialization parameter and warm start.

ORA-00055 maximum number of DML locks exceeded:

This tends to be an intermittent problem, and occurs at moments of peak usage in an Oracle database.

It can be resolved by changing a parameter in the "initSID.ora" file:

dml_locks = 200

Basically, if the DML Locks limit has been set at 200, then

. 200 people could each be updating one table at a time
· or 20 people could all be updating 10 tables at a time,
· or 1 user could be doing an account number rename and using 100 tables while 10 other people could be updating 10 tables.

These updates do not need to be the same table.

The number of locks required increased from Oracle8 onwards since partitioned tables would use a lock for each partition.

No comments: