How to find the session and SQL statement that is not closing the ResultSet?

There was an issue the other day where the application session ran out of open cursors resulting the following error
Exception: java.sql.SQLException: ORA-01000: maximum open cursors exceeded

So using the following SQL we found the session had reached the max limit of cursors (open_cursors) and thereby found the SQL which was causing the issue and
SQL> select s.username, s.sid, a.value
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current'
and s.username = 'SCOTT';

USERNAME SID COUNT(1)
—————————— ———- ———-
SCOTT 143 17
SCOTT 89 1000

To display the current limit set in the database
SQL> show parameter open_cursors

NAME TYPE VALUE
———————————— ———– ——-
open_cursors integer 1000

So using V$open_cursor and joining with V$session we were able to find the SQL that wasn’t closing the cursor.
select vo.sql_id, count(1) from v$open_cursor vo, v$session vs
where vs.sid = vo.sid and vs.username = 'SCOTT'
group by vo.sql_id order by count(1);

SQL_ID COUNT(1)
————- ———-

9gs5t9bj7q686 21
2na94tfc9ukuk 1000

SQL> select sql_text from v$sqltext where sql_id = ‘2na94tfc9ukuk’;

SQL_TEXT
—————————————————————-
select * from table1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s