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