Urgent Error :ORA-00052: maximum number of enqueue resources (5000) exceeded

[pdh]

Hi Guys,

Error :ORA-00052: maximum number of enqueue resources (5000) exceeded


I am encountering the above error while trying to create a a partition table. Can anybody explain what is this Enqueue resouces is and how to rectify it .

[mrvajrala]

Would you check the value for the parameter
ENQUEUE_RESOURCES..

and also the number of tables( all schemas ) in your database ?? pl post the values here

[pdh]

Hi Madhu,

Thanks for replying, I have the init parameter enqueue_paramter set to 5000, and the no. of tables in all schemas put together are 40. Most of the tables are partition tables and each table has about 45 partitions. About 15 tables have partitions.

Thanks for replying,

[mrvajrala]

The present value itself is HUGE ( as it appears to me )..

Basically to solve this problem .. Oracle suggests to increase the value of this parameter.. This problem can also be a temporary one. Once the locks are relased , then you can proceed with the desired operation...

If this problem is very frequent , then you may wish to increase the value fo this parameter.

and also query the view v$resource_limit... there also you might see the figure 5000, for the same parameter.

Post all your observations

[Knarayan]

Cause: An operation requested a resource that was unavailable.
The maximum number of enqueue resources is specified by the
initialization parameter ENQUEUE_RESOURCES.
When this maximum is reached, no more requests are processed.
Action: Try the operation again in a few minutes.
If this message occurs often, shut down Oracle, increase the
ENQUEUE_RESOURCES parameter in the initialization parameter file, and
restart Oracle.

Hope this helps

[pdh]

Thanks Guys,

The problem was persistent, so I increased the enque_resources to 10000 and ran the script to create the tables it worked fine. Thanks for all your help.