Identify cursor leak in Oracle

As a developer it is important to know 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."


We can identify these type of leaks by using some tools but this is a bit hectic to identify in code level.

So the solution is we can diagnose these from oracle database side with a minimal queries...


1. Identify the session by its Oracle username to retrieve the sid value.


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


sid

-----------

1234


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 FROM v$open_cursor WHERE sid = 1234 GROUP BY address HAVING COUNT(address) > 1 ORDER BY COUNT(*);


COUNT(*) ADDRESS

---------------- ----------------

2 35E6083C

2 35B77834

2 35E686B4

3 35F97908


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_length;