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