List open cursors per user

List open cursors per user

set pages 999
select  sess.username
, sess.sid
, sess.serial#
, stat.value cursors
from v$sesstat stat
, v$statname sn
, v$session sess
where sess.username is not null
and sess.sid = stat.sid
and stat.statistic# = sn.statistic#
and sn.name = 'opened cursors current'
order by value
/

Output:

USERNAME               SID    SERIAL#    CURSORS
--------------- ---------- ---------- ----------
SYS                    143         10          1
WEBUSER                132      33364          3
WEBUSER                116      18996          3
WEBUSER                118      23832          3
WEBUSER                120        423          3
ORA_TIV                126      37072          3
WEBUSER                127       4392          3
WEBUSER                124      20787          4
SYS                    131      34710          5
CNUSERMAN              145      19887          5
CNUSERMAN              129       8407          5
CNUSERMAN              144      17898          5
CNUSERMAN              141       3881          5
ORA_TIV                139       5349          8
ORA_TIV                115       6676          9

15 rows selected.