HOW TO
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.
Code:
SQL> SELECT sid FROM v$session WHERE username = 'TOM';
SID
----------
135
Code:
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
Code:
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
Article ID:000010136
Get help from ArcGIS experts
Download the Esri Support App