ORA-01000: maximum open cursors exceeded due to “Cursor leak”

The maximum open cursors (handles to private SQL areas) that a session can have at once are specified by OPEN CURSORS. This setting allows you to limit the amount of cursors that a session opens.

It’s crucial to increase the value of OPEN CURSORS so that your application doesn’t run out of open cursors. Depending on the use, the quantity will change. There is no additional overhead to setting this value higher than necessary, assuming that a session does not open the number of cursors indicated by OPEN CURSORS.

  • Application executes a number of SQL statements, that uses the Oracle Database as a backend repository.
  • In Oracle Database, a specific amount of memory is allocated for each SQL statement that is executed.
  • Context area or cursor are the names for this private SQL space.
  • These cursors consume memory in the library cache of Oracle Database’s shared pool, a crucial memory component.
  • The OPEN CURSORS database parameter must be set to limit the number of cursors in order to prevent a renegade session from overloading the CPU with millions of parse requests or filling up the library cache.
  • The maximum number of cursors that can be open during each session is determined by the OPEN CURSORS option.
  • Example: A session may have up to 300 open cursors at once, for instance, if the value of OPEN CURSORS is set to 300.

ORA-01000 error code is thrown, when an individual session reaches the hard limit for open cursors as per open_cursors parameter. Default value is 300.

declare
*
ERROR at line 1:
ORA-01000: maximum open cursors exceeded
ORA-06512: at line 18

Check the existing value of the open cursor parameter

kIsH@Xhydra<>show parameter open_cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     200

A particular procedure executes for a long time without closing the cursor and hence open cursor reached 190. Limit is only 200. Hence some times it may be even application code fault of not efficiently closing the cursor.

kIsH@Xhydra<>set lines 200 pages 1000
col name for a30
col current_value for 9999999999999
col total_value for a20
select st.name,
       max(ses.value) "current_value",
           p.value "total_value",
              round(100 * (max(ses.value) / p.value)) "PCT"
from v$statname st
inner join v$sesstat ses on st.statistic# = ses.statistic#
cross join v$parameter p
where p.name='open_cursors'
and
st.name='opened cursors current'
group by st.name,p.value;kIsH@Xhydra<>kIsH@Xhydra<>kIsH@Xhydra<>kIsH@Xhydra<>  2    3    4    5    6    7    8    9   10   11

NAME                            current_value total_value                 PCT
------------------------------ -------------- -------------------- ----------
opened cursors current                     190 200                          95

Filter the SQL which consume many cursor

kIsH@Xhydra<>select sid,
       sql_id,
           count(*) ocursors
from v$open_cursor
group by sid,sql_id
having count(*) > 3
order by count(*) desc;  2    3    4    5    6    7

       SID SQL_ID          OCURSORS
---------- ------------- ----------
        92 f3q3fhgz2qmhk        160
        34 16jd1y3vpvfbt          8
        34 avvfapa4gfgc4          4
        34 66tyyrdwrbmx7          4
        34 0qv27wt724y7s          4

--After the error is triggered, the job is failed and cursors are released
kIsH@Xhydra<>select sid,
       sql_id,
       address,
       count(*) ocursors
from v$open_cursor
group by sid,sql_id,address
having count(*) > 3
order by count(*) desc;
  2    3    4    5    6    7    8
      SID SQL_ID        ADDRESS                OCURSORS
--------- ------------- -------------------- ----------
       41 f3q3fhgz2qmhk 0000000071C97F00             48
       42 16jd1y3vpvfbt 000000006ADCC9B0              4
      374 frccccnh76gtx 0000000068D94A58              4
      347 aj14ngnvpkk6t 000000006828E168              4
      347 bxpcry2tpc217 0000000065B09E68              4

Identify the sql text which is responsible. This statement should tuned in terms of PLSQL procedure calls for consuming less cursors.

kIsH@Xhydra<>select sql_text from v$sql where sql_id='f3q3fhgz2qmhk';

SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT "A2"."PK" "PK","A2"."FK" "FK" FROM "OC" "A2" WHERE :CV1$="A2"."FK"
SELECT "A2"."PK" "PK","A2"."FK" "FK" FROM "OC" "A2" WHERE :CV1$="A2"."FK"

Trace file shows the stacks of the failing procedure which can be utilized to identify a potential bugs which are related to the error.

ORA-01000: maximum open cursors exceeded
----- SQL Statement (None) -----
Current SQL information unavailable - no cursor.
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x6e789520        18  anonymous block

----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
ksedst1()+95         call     kgdsdst()            7FFCC5E8B910 000000002
                                                   7FFCC5E85C40 ? 7FFCC5E85D58 ?
                                                   000000000 000000082 ?
ksedst()+58          call     ksedst1()            000000000 000000001
                                                   7FFCC5E85C40 ? 7FFCC5E85D58 ?
                                                   000000000 ? 000000082 ?
dbkedDefDump()+2308  call     ksedst()             000000000 000000001 ?
0                                                  7FFCC5E85C40 ? 7FFCC5E85D58 ?
                                                   000000000 ? 000000082 ?
ksedmp()+577         call     dbkedDefDump()       00000000C 000000000
                                                   7FFCC5E85C40 ? 7FFCC5E85D58 ?
                                                   000000000 ? 000000082 ?
dbkdaKsdActDriver()  call     ksedmp()             00000000C 000000000 ?
+2484                                              7FFCC5E85C40 ? 7FFCC5E85D58 ?
                                                   000000000 ? 000000082 ?
dbgdaExecuteAction(  call     dbkdaKsdActDriver()  7F324A4846D0 7FFCC5E8DDA0
)+354                                              7FFCC5E85C40 ? 7FFCC5E85D58 ?
                                                   000000000 ? 000000082 ?
dbgdaRunAction()+76  call     dbgdaExecuteAction(  7F324A4846D0 0140B5E00
2                             )                    044174BC0 7FFCC5E8DDA0
                                                   000000001 000000082 ?
dbgdRunActions()+83  call     dbgdaRunAction()     7F324A4846D0 7FFCC5E8E020
                                                   7F324A4846D0 000000000
                                                   7FFCC5E8E028 000000082 ?
dbgdProcessEventAct  call     dbgdRunActions()     7F324A4846D0 ? 7FFCC5E8E020 ?
ions()+525                                         7F324A4846D0 ? 000000000 ?
                                                   7FFCC5E8E028 ? 000000082 ?
dbgdChkEventKgErr()  call     dbgdProcessEventAct  7F324A4846D0 7F324A4C49A0
+352                          ions()               7F3244174E58 000000000 ?
                                                   7FFCC5E8E028 ? 000000082 ?
dbkdChkEventRdbmsEr  call     dbgdChkEventKgErr()  7F324A4846D0 ? 7F324A4C49A0 ?
r()+65                                             7F3244174E58 ? 000000000 ?
                                                   7FFCC5E8E028 ? 000000082 ?
dbgePostErrorKGE()+  call     dbkdChkEventRdbmsEr  7F324A4846D0 ? 0000003E8
1066                          r()                  7F3244174E58 ? 000000000 ?
                                                   7FFCC5E8E028 ? 000000082 ?
dbkePostKGE_kgsf()+  call     dbgePostErrorKGE()   7F324A4C49A0 7F3244E0DA20
71                                                 0000003E8 000000000 ?
                                                   7FFCC5E8E028 ? 000000082 ?
kgeade()+392         call     dbkePostKGE_kgsf()   7F324A4C49A0 7F3244E0DA20
                                                   0000003E8 000000000 ?
                                                   7FFCC5E8E028 ? 000000082 ?
kgeselv()+89         call     kgeade()             7F324A4C49A0 ? 7F324A4C4BE8 ?
                                                   7F3244E0DA20 ? 0000003E8 ?
                                                   000000000 7F3200000000
ksesecl0()+189       call     kgeselv()            7F324A4C49A0 ? 7F3244E0DA20 ?
                                                   0000003E8 ? 013D1226C
                                                   013D1227C 000000000
kxsOpenACursor()+88  call     ksesecl0()           7F324A4C49A0 ? 7F3244E0DA20 ?
4                                                  0000003E8 ? 000000000
                                                   7F324A5153C0 7F324A3E2570
opiopr()+228         call     kxsOpenACursor()     7F324A4C49A0 ? 000000000 ?
                                                   0000003E8 ? 000000000 ?
                                                   7F324A5153C0 ? 7F324A3E2570 ?
opiodr()+1202        call     opiopr()             7F324A4C49A0 ? 000000000 ?
                                                   0000003E8 ? 000000000 ?
                                                   7F324A5153C0 ? 7F324A3E2570 ?
rpidrus()+198        call     opiodr()             000000064 000000003
                                                   0000003E8 ? 000000000
                                                   7F324A5153C0 ? 7F324A3E2570 ?
skgmstack()+65       call     rpidrus()            7FFCC5E8FA38 000000003 ?
                                                   7F324A4C4BE8 000000000 ?
                                                   7F324A5153C0 ? 7F324A3E2570 ?
rpidru()+132         call     skgmstack()          7FFCC5E8FA38 ? 000000003 ?
                                                   7F324A4C4BE8 ? 000000000 ?
                                                   7F324A5153C0 ? 7F324A3E2570 ?
rpiswu2()+541        call     rpidru()             7FFCC5E902C8 ? 000000003 ?
                                                   7F324A4C4BE8 ? 000000000 ?
                                                   7F324A5153C0 ? 7F324A3E2570 ?
rpidrv()+1248        call     rpiswu2()            7FFCC5E902C8 ? 000000003 ?
                                                   7F324A4C4BE8 ? 000000000 ?
                                                   7F324A5153C0 ? 7F324A3E2570 ?
rpiope()+65          call     rpidrv()             000000000 000000064
                                                   7FFCC5E903C0 000000019
                                                   7F324A5153C0 ? 7F324A3E2570 ?
kokbenc()+115        call     rpiope()             004000401 000000064 ?
                                                   7FFCC5E903C0 ? 000000019 ?
                                                   7F324A5153C0 ? 7F324A3E2570 ?
evaopn2()+737        call     kokbenc()            072636BF0 ? 000000064 ?
                                                   7FFCC5E903C0 ? 000000019 ?
                                                   7F324A5153C0 ? 7F324A3E2570 ?
opifcr()+552         call     evaopn2()            072636BF0 ? 000000064 ?
                                                   7FFCC5E903C0 ? 000000019 ?
                                                   7F324A5153C0 ? 7F324A3E2570 ?
kdstf00001010000000  call     opifcr()             072636BF0 ? 000000064 ?
0km()+661                                          7F324A4C49A0 000000019 ?
                                                   7F324A5153C0 ? 7F324A3E2570 ?
kdsttgr()+2214       call     kdstf00001010000000  7F3244177040 000000000
                              0km()                0123FF4C0 7FFCC5E92620
                                                   000000001 7FFCC5E915F8
qertbFetch()+1089    call     kdsttgr()            7F3244177040 ? 000000000 ?
                                                   000000000 ? 7FFCC5E92620 ?
                                                   000000001 ? 7FFCC5E915F8 ?
opifch2()+3206       call     qertbFetch()         7F3244177040 ? 000000000 ?
                                                   0123FF4C0 7FFCC5E92620 ?
                                                   000000001 ? 7FFCC5E915F8 ?
opifch()+61          call     opifch2()            7F3244177040 ? 000000000 ?
                                                   7FFCC5E927B0 7FFCC5E92620 ?
                                                   000000001 ? 7FFCC5E915F8 ?
opipls()+5554        call     opifch()             7F3244177040 ? 000000000 ?
                                                   7FFCC5E927B0 ? 000000001
                                                   000000000 012400002
opiodr()+1202        call     opipls()             7F3244177040 ? 000000000 ?
                                                   7FFCC5E94F60 000000001 ?
                                                   7F324A3E2570 012400002 ?
rpidrus()+198        call     opiodr()             000000066 000000004
                                                   7FFCC5E94F60 ? 000000002
                                                   7F324A3E2570 ? 012400002 ?
skgmstack()+65       call     rpidrus()            7FFCC5E93C98 000000004 ?
                                                   7F324A4C4BE8 000000002 ?
                                                   7F324A3E2570 ? 012400002 ?
rpidru()+132         call     skgmstack()          7FFCC5E93C98 ? 000000004 ?
                                                   7F324A4C4BE8 ? 000000002 ?
                                                   7F324A3E2570 ? 012400002 ?
rpiswu2()+541        call     rpidru()             7FFCC5E94528 ? 000000004 ?
                                                   7F324A4C4BE8 ? 000000002 ?
                                                   7F324A3E2570 ? 012400002 ?
rpidrv()+1248        call     rpiswu2()            7FFCC5E94528 ? 000000004 ?
                                                   7F324A4C4BE8 ? 000000002 ?
                                                   7F324A5153C0 ? 012400002 ?
psddr0()+467         call     rpidrv()             000000002 000000066
                                                   7FFCC5E94F60 000000039
                                                   7F324A5153C0 ? 012400002 ?
psdnal()+624         call     psddr0()             000000002 000000066
                                                   7FFCC5E94F60 000000030
                                                   7F324A4C4BE8 000000001
pevm_BFTCHC()+314    call     psdnal()             7FFCC5E95EC0 000000066 ?
                                                   000000002 7F324417DBE8
                                                   000000000 ? 000000001 ?
pfrinstr_FTCHC()+13  call     pevm_BFTCHC()        7F3244174630 ? 7F324A3A6CA0
5                                                  000000000 ? 7F324417DBE8 ?
                                                   000000000 ? 000000001 ?
pfrrun_no_tool()+52  call     pfrinstr_FTCHC()     7F3244174630 ? 0724271E0
                                                   7F32441746A0 ? 7F324417DBE8 ?
                                                   000000000 ? 000000001 ?
pfrrun()+902         call     pfrrun_no_tool()     7F3244174630 ? 0724271E0 ?
                                                   7F32441746A0 ? 7F324417DBE8 ?
                                                   000000000 ? 000000001 ?
plsql_run()+752      call     pfrrun()             7F3244174630 0724271E0 ?
                                                   7F32441746A0 ? 7FFCC5E95EC0
                                                   06F16732A 000000001 ?
peicnt()+282         call     plsql_run()          7F3244174630 000000001
                                                   000000000 7FFCC5E95EC0 ?
                                                   06F16732A ? 000000001 ?
kkxexe()+720         call     peicnt()             7FFCC5E95EC0 7F3244174630
                                                   000000000 ? 7FFCC5E95EC0 ?
                                                   06F16732A ? 000000001 ?
opiexe()+24817       call     kkxexe()             7F324417E478 7F3244174630 ?
                                                   000000000 ? 7FFCC5E95EC0 ?
                                                   000000001 000000001 ?
kpoal8()+2387        call     opiexe()             000000049 7F3244174630 ?
                                                   7FFCC5E97840 7FFCC5E95EC0 ?
                                                   000000001 ? 000000001 ?
opiodr()+1202        call     kpoal8()             00000005E 000000026
                                                   7FFCC5E9B2B0 7FFCC5E95EC0 ?
                                                   000000001 ? 000000001 ?
ttcpip()+1222        call     opiodr()             00000005E 000000026
                                                   7FFCC5E9B2B0 ? 000000000
                                                   000000001 ? 000000001 ?
opitsk()+1895        call     ttcpip()             7F324A4EEB10 ? 0000005E0 ?
                                                   7FFCC5E9B2B0 000000000 ?
                                                   7FFCC5E9AD10 7FFCC5E9B4FC
opiino()+936         call     opitsk()             000000000 000000000
                                                   7FFCC5E9B2B0 ? 000000000 ?
                                                   7FFCC5E9AD10 ? 7FFCC5E9B4FC ?
opiodr()+1202        call     opiino()             00000003C 000000004
                                                   7FFCC5E9CE98 000000000 ?
                                                   7FFCC5E9AD10 ? 7FFCC5E9B4FC ?
opidrv()+1094        call     opiodr()             00000003C 000000004
                                                   7FFCC5E9CE98 ? 000000000
                                                   7FFCC5E9AD10 ? 7FFCC5E9B4FC ?
sou2o()+165          call     opidrv()             00000003C 000000004
                                                   7FFCC5E9CE98 000000000 ?
                                                   7FFCC5E9AD10 ? 7FFCC5E9B4FC ?
opimai_real()+422    call     sou2o()              7FFCC5E9CE70 00000003C
                                                   000000004 7FFCC5E9CE98
                                                   7FFCC5E9AD10 ? 7FFCC5E9B4FC ?
ssthrdmain()+417     call     opimai_real()        000000000 7FFCC5E9D688
                                                   000000004 ? 7FFCC5E9CE98 ?
                                                   7FFCC5E9AD10 ? 7FFCC5E9B4FC ?
main()+256           call     ssthrdmain()         000000000 000000002
                                                   7FFCC5E9D688 000000001
                                                   000000000 7FFCC5E9B4FC ?
__libc_start_main()  call     main()               000000002 7FFCC5E9D898
+243                                               7FFCC5E9D688 ? 000000001 ?
                                                   000000000 ? 7FFCC5E9B4FC ?
_start()+46          call     __libc_start_main()  000DA7880 000000002
                                                   7FFCC5E9D898 006A35C30 ?
                                                   000000000 ? 7FFCC5E9B4FC ?

Failing stack show the function kxsOpenACursor() which calls ksesecl0()

========== FRAME [17] (kxsOpenACursor()+884 -> ksesecl0()) ==========
defined by frame pointers 0x7ffcc5e8ed40  and 0x7ffcc5e8ecc0
CALL TYPE: call   ERROR SIGNALED: yes   COMPONENT: cursor
RDI 00007F324A4C49A0 RSI 00007F3244E0DA20 RDX 00000000000003E8 
RCX 0000000000000000 R8 00007F324A5153C0 R9 00007F324A3E2570 
RAX 0000000000000000 RBX 0000000000000000 RBP 00007FFCC5E8ED40 
R10 00007FFCC5E8EC90 R11 0000000000000000 R12 00007F324A5153C0 
R13 0000000000000000 R14 00007F324A515301 R15 00007F324A3E2570 
RSP 00007FFCC5E8ECD0 RIP 0000000012572AA4 
Dump of memory from 0x7ffcc5e8ecc0 to 0x7ffcc5e8ed40
7FFCC5E8ECC0 C5E8ED40 00007FFC 12572AA4 00000000  [@........*W.....]
7FFCC5E8ECD0 4A3E2570 00007F32 00000000 00000000  [p%>J2...........]
7FFCC5E8ECE0 C5E8ED20 00007FFC 4A5153C0 00007F32  [ ........SQJ2...]
7FFCC5E8ECF0 44019E00 00007F32 6D0FE0C8 00000000  [...D2......m....]
7FFCC5E8ED00 4A5153C0 00007F32 7F7E6738 00000000  [.SQJ2...8g~.....]
7FFCC5E8ED10 7F7E6738 00000000 4A515301 00007F32  [8g~......SQJ2...]
7FFCC5E8ED20 4A4C49A0 00007F32 4A5153C0 00007F32  [.ILJ2....SQJ2...]
7FFCC5E8ED30 00000000 00000000 00000001 00000000  [................]

Solution:

From DBA point of view, there are ways to increase the below parameter or try to tune the sql as per database version.

alter system set open_cursors=1000 scope=spfile;
or
alter system set optimizer_features_enable=12.1.0.2;

Will keep on increase the parameter if the error occur recursively? That is not always feasible as a long term proactive fix! Hence schedule a call and discuss this with application team. Identify the session details and report the module of application with SQL statement to the developer.

kIsH@Xhydra<>col MODULE for a20
kIsH@Xhydra<>col ACTION for a20
kIsH@Xhydra<>col PROGRAM for a20
kIsH@Xhydra<>select sid,terminal,module,action,program from v$session where SID=35;

       SID TERMINAL                       MODULE               ACTION               PROGRAM
---------- ------------------------------ -------------------- -------------------- --------------------               APP                 task44
        92 UNKNOWN                                                                  oracle@xhydra (JDBC Thin client)

But optimal solution is to close the application cursors properly in the code

Example: close cur;

Leave a Reply