Identify a cursor leak in Oracle

Summary

This article describes how to identify which Oracle cursor is not being closed or reused on each execution that leads to the following Oracle error:

"ORA-01000: maximum open cursors exceeded."

It is important to be able to diagnose which cursor is being 'leaked' (not closed) to identify what part of the application is responsible for managing the cursor, Oracle, ArcGIS, or the application developer.

Procedure

Instructions provided demonstrate how to identify cursors that are not being closed or reused by an application for a given Oracle session.

1. Identify the session by its Oracle username to retrieve the sid value. Connect to the Oracle instance as the SYS or SYSTEM or a user with DBA privileges using SQL*Plus. Execute the following SQL statement to obtain the session's sid value:

SQL> SELECT sid FROM v$session WHERE username = 'TOM';

       SID
----------
       135

The sid value is used in the next step to identify the session to investigate.

2. 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.

SQL> SELECT COUNT(*), address
  2  FROM v$open_cursor
  3  WHERE sid = 135
  4  GROUP BY address HAVING COUNT(address) > 1 ORDER BY COUNT(*);

  COUNT(*) ADDRESS
---------- --------
         2 35E6083C
         2 35B77834
         2 35E686B4
         3 35F97908

The result lists each cursor, which has been opened by the session more than once in descending order.

3. Obtain the actual SQL statement for each address returned in step 2. This can be accomplished by querying the v$sql view using the address value.

SQL> SELECT sql_fulltext
  2  FROM v$sql
  3  WHERE address = '35F97908';

SQL_FULLTEXT
-------------------------------------------------------------------------------
SELECT  OBJECTID, SEG_ID, SYMBOL, PIPE_SIZE, SLOPE, US_INV,
DS_INV, ACC_NO, SEW_NO, MATERIAL, SEW_SHAPE, HEIGHT, WIDTH,
INST_YEAR, TV_LAST, DRAIN_AREA, MEAS_REF, SEP_COMB, PUB_PRI,
QUEST,  SEG_TYPE, SSAD_LEN, V__43.st_SHAPE$, V__43.st_len$, 
V__43.st_points, V__43.st_numpts, V__43.st_entity, V__43.st_minx,
V__43.st_miny, V__43.st_maxx,V__43.st_maxy, V__43.st_area$,
V__43.st_len$, V__43.st_rowid FROM
(SELECT b.OBJECTID, b.SEG_ID, b.SYMBOL, b.PIPE_SIZE, b.SLOPE, b.US_INV,
b.DS_INV, b.ACC_NO, b.SEW_NO, b.MATERIAL, b.SEW_SHAPE,
b.HEIGHT, b.WIDTH, b.INST_YEAR, b.TV_LAST, b.DRAIN_AREA,
b.MEAS_REF, b.SEP_COMB, b.PUB_PRI, b.QUEST, b.SEG_TYPE,
b.SSAD_LEN, 1 st_SHAPE$, b.SHAPE.points as st_points,
b.SHAPE.numpts as st_numpts, b.SHAPE.entity as st_entity, b.SHAPE.minx as st_minx, 
b.SHAPE.miny as st_miny, b.SHAPE.maxx as st_maxx, b.SHAPE.maxy as st_maxy,
b.SHAPE.area as st_area$, b.SHAPE.len as
st_len$, b.rowid as st_rowid FROM TOM.sewers b
WHERE SDE.ST_EnvIntersects(b.SHAPE,:1,:2,:3,:4) = 1
AND b.OBJECTID NOT IN (SELECT /*+ HASH_AJ */ SDE_DELETES_ROW_ID
FROM TOM.D43 WHERE DELETED_AT IN (SELECT l.lineage_id
FROM SDE.state_lineages l WHERE l.lineage_name = :lineage_name1 AND l.lineage_id <= :state_id1)
AND SDE_STATE_ID = 0) UNION ALL SELECT a.OBJECTID, a.SEG_ID, a.SYMBOL, a.PIPE_SIZE, a.SLOPE,
a.US_INV, a.DS_INV, a.ACC_NO, a.SEW_NO, a.MATERIAL, a.SEW_SHAPE,
a.HEIGHT, a.WIDTH, a.INST_YEAR, a.TV_LAST, a.DRAIN_AREA,
a.MEAS_REF, a.SEP_COMB, a.PUB_PRI, a.QUEST, a.SEG_TYPE, a.SSAD_LEN, 2
st_SHAPE$ ,a.SHAPE.points as st_points, a.SHAPE.numpts as st_numpts,
a.SHAPE.entity as st_entity, a.SHAPE.minx as st_minx, a.SHAPE.miny as st_miny,
a.SHAPE.maxx as st_maxx, a.SHAPE.maxy as st_maxy, a.SHAPE.area as st_area$,
a.SHAPE.len as st_len$, a.rowid as st_rowid FROM TOM.A43 a, SDE.state_lineages SL
WHERE SDE.ST_EnvIntersects(a.SHAPE,:5,:6,:7,:8) = 1 
AND (a.OBJECTID, a.SDE_STATE_ID) NOT IN (SELECT /*+ HASH_AJ */ SDE_DELETES_ROW_ID, SDE_STATE_ID
FROM TOM.D43 WHERE DELETED_AT IN (SELECT  l.lineage_id FROM
SDE.state_lineages l WHERE l.lineage_name = :lineage_name2 AND l.lineage_id <= :state_id2)
AND SDE_STATE_ID > 0) AND a.SDE_STATE_ID = SL.lineage_id AND SL.lineage_name
= :lineage_name3 AND SL.lineage_id <= :state_id3) V__43

The result, in this scenario, is a versioned spatial query. Based on what the application is doing, possibly custom ArcObjects, there are three references to this one SQL statement. Either the application is opening three explicit references or the ArcGIS application is not reusing the initial SQL statement.
If further assistance is required in identifying the cause of the problem, contact ESRI Technical Support.