How to display open cursors in Oracle?


You want to display open cursors in Oracle.


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


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