Identify a Cursor Leak in Oracle

How to Identify a cursor leak in Oracle

Here we explore how to find out which Oracle cursor is not being timely closed or reused on each execution
This could lead to error in Oracle such as:

ORA-01000: maximum open cursors exceeded.

• As a developer / DBA, we should be able to investigate which cursor is being left open.
• Once we do this it would be simpler to find out which part of the application is causing the cursor issue.
• Whether it is the 3rd party application, Oracle, ArcGIS, or the application developer.

Steps

Here we will try to find cursors that are being left open or not reused by an application.

• Distinguish the session by its Oracle username to get back the sid value.
• Attach to the Oracle instance as the SYS or SYSTEM or a user with DBA privileges using SQL*Plus.
• Then run the below SQL command to get the session's sid number:

SELECT sid FROM v$session WHERE username = 'SUYASH';

       SID
----------
       172

• The sid number is then used to find out the session.
• List the session's SQL statement addresses, which have more than one active reference.
• Using the sid value from the previous statement, execute a query against the v$open_cursor view.

SELECT COUNT(*), address FROM v$open_cursor WHERE sid = 172 GROUP BY address HAVING COUNT(address) > 1 ORDER BY COUNT(*);

  COUNT(*) ADDRESS
---------- --------
         4 27A8755B
         4 32B77834
         4 27A886E1
         8 32F98675

• The output here will show every cursor, which has been initiated by the session with minimum of 4 instances.
• Now we can get the original SQL command for each address returned here.
• To do this we need to query the v$sql view using the address number.

SELECT sql_fulltext FROM v$sql WHERE address = '32B77834';

The output here would be a multi-dimensional command.
Depending on what the application is doing, possibly custom objects, there are three or more references to this one SQL statement.
Either the application is opening three explicit references, or the application is not reusing the initial SQL statement.

Ref: https://support.esri.com/en-us/knowledge-base/how-to-identify-a-cursor-leak-in-oracle-000010136