IBM Support

Oracle cursors maxing out and resulting in ORA-1000 error for database queries from application

Troubleshooting


Problem

Oracle cursors maxing out and resulting in ORA-1000 error for database queries from application

Symptom

Oracle cursors maxing out and resulting in ORA-1000 error for database queries from application

Error Message

        <Error ErrorCode="ORA-1000" ErrorDescription="ORA-01000: maximum open cursors exceeded " ErrorRelatedMoreInfo="">
        <Attribute Name="ErrorCode" Value="ORA-1000"/>
        <Attribute Name="ErrorDescription" Value="ORA-01000: maximum open cursors exceeded "/>
        <Attribute Name="SQLStatement" Value=" SELECT  /*YANTRA*/   YFS_SHIPMENT.* FROM YFS_SHIPMENT YFS_SHIPMENT     WHERE ( ( (  ( YFS_SHIPMENT.EXTN_REFERENCE_1 = '0650321465'  )  ) ))  ORDER BY SHIPMENT_NO ,SHIPNODE_KEY ,SELLER_ORGANIZATION_CODE "/>
        <Attribute Name="SQLState" Value="72000"/>
        <Attribute Name="DBErrorCode" Value="1000"/>
        <Attribute Name="TransactionIsolationLevel" Value="2"/>
        <Error ErrorCode="java.sql.SQLException" ErrorDescription="" ErrorRelatedMoreInfo="ORA-01000: maximum open cursors exceeded ">
            <Stack>java.sql.SQLException: ORA-01000: maximum open cursors exceeded

Resolving The Problem

 
If the application code does not close the statements/result sets properly, then this leaves the cursors open, unless the transaction boundary takes care of it or one restarts the application.

 
The open cursors can lead to the problem of cursors maxing out. Hence, one should clean the result set for every statement/result set to avoid such scenarios.
Custom code level calls can result in such situations if the above is not taken care during coding.
 
OPEN_CURSORS: A cursor is a pointer used to fetch rows from a result set. One can think of a cursor as a data structure that describes the results returned from a SQL SELECT statement. One of the variables in this structure is a pointer to the next record to be fetched from the query results. Note that if one does repetitive stuff inside a loop and fails to close the cursors, one is likely to run into the ORA-01000: maximum number of open cursors exceeded error.
 
 
Query to find the OPEN_CURSORS set value:
select * from v$parameter where name = 'open_cursors';
 
Query to find all the open cursors:
select user_name, status, osuser, machine, a.sql_text
from v$session b, v$open_cursor a
where a.sid = b.sid;
 
Query to find actual (ACTIVE) open cursors:
select a.value, b.name
from v$mystat a, v$statname b
where a.statistic# = b.statistic# and a.statistic#= 3;
 
Query to find the open cursors:
select * from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#
and s.sid=a.sid
and b.name = 'opened cursors current'
and machine = 'Machine_name';

[{"Product":{"code":"SS6QYM","label":"Sterling Selling and Fulfillment Suite"},"Business Unit":{"code":"BU055","label":"Cognitive Applications"},"Component":"Not Applicable","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All","Edition":"","Line of Business":{"code":"LOB59","label":"Sustainability Software"}}]

Historical Number

NFX5809

Product Synonym

[<p><b>]Severity[</b><p>];Normal

Document Information

Modified date:
16 June 2018

UID

swg21553051

Page Feedback