Skip to Main Content
  • Questions
  • Close cursors, reduce session_cached_cursors value

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: June 13, 2017 - 1:43 pm UTC

Last updated: June 17, 2017 - 1:20 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Chris/Connor

Can you please help to understand below issue and steps to follow to identify root cause.

04025, 00000, "maximum allowed library object lock allocated for %s%s%s%s%s"
// *Cause: Too many active locks for the object has been allocated. This error
// can be a result of too many cursors kept open per session.
// *Action: Close cursors, reduce session_cached_cursors value.

and Connor said...

You could be hitting this bug

Bug 14542720 - ORA-4025 with CURSOR_SHARING / Dynamic Sampling

See MOS note 14542720.8 for details.

Or if you have a huge number of concurrent sessions all actively using the same PL/SQL routine, you might run into it. If this is the case, then the action suggest (reduce session_cached_cursors value) is probably your solution (if you cant change the app)

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

More Inputs on AWR

A reader, June 16, 2017 - 10:34 am UTC

Can you please have a look at below AWR details:
Will these top SQl's may cause this issue? 

SQL ordered by Elapsed Time & SQL ordered by CPU Time
Elapsed Time (s) Executions Elapsed Time per Exec (s) %Total %CPU %IO SQL Id SQL Module SQL Text
10,810.77 0  22.79 62.64 0 47vfcq2303w31 SQL Developer /* + NO_PARALLEL */select coun...
10,810.77 0  22.79 62.6 0 dpuvytrq0qwvk SQL Developer /* + NO_PARALLEL */select coun...
10,810.26 0  22.79 60.95 0 49t1bdq6tuq3f JDBC Thin Client SELECT Mcm.Request_Id AS Reque...
6,359.23 31,288 0.2 13.4 62.54 0 664j0vtzy3z30 JDBC Thin Client SELECT tab.* from (SELECT rown...

SQL ordered by Sharable Memory
Sharable Mem (b) Executions % Total SQL Id SQL Module SQL Text
245,249,592  2.21 3k6a4mkw4vp4q  /*+ NO_SQL_TRANSLATION */ SELE...
199,115,240  1.8 00m5mvdr85rwm  SELECT :"SYS_B_0" type, userna...
75,745,320  0.68 9m82p8p2d7z84  ** SQL Text Not Available **
48,056,640  0.43 1wdhux6669jk1  ** SQL Text Not Available **
37,235,760  0.34 1cczmcd1fdfzm  /*+ NO_SQL_TRANSLATION */ SELE...
36,127,568  0.33 cnumpcz520unc  ** SQL Text Not Available **
31,955,864  0.29 bn4b3vjw2mj3u  SELECT OBJOID, CLSOID, DECODE(...
23,313,768  0.21 6wm3n4d7bnddg  SELECT source, (case when tim...
23,092,736  0.21 15aftu6tf4xd0  ** SQL Text Not Available **
22,506,840  0.2 3mqvkt9as1phq  ** SQL Text Not Available **

Connor McDonald
June 17, 2017 - 1:20 am UTC

Best to contact Support - they'll lead you through the diagnostic process