How to display open cursors in Oracle?

Problem:

You want to display open cursors in Oracle.

Solution

We can query the data dictionary to determine the number of cursors that are open per session. "V$SESSION" provides a more accurate number of the cursors currently open than "V$OPEN_CURSOR".

Example

select
 a.value
 ,c.username
 ,c.machine
 ,c.sid
 ,c.serial#
from v$sesstat a
 ,v$statname b
 ,v$session c
where a.statistic# = b.statistic#
and c.sid  = a.sid
and b.name  = 'opened cursors current'
and a.value  != 0
and c.username IS NOT NULL
order by 1,2;

The OPEN_CURSORS initialization parameter determines the maximum number of cursors a session can have open.