SQL> desc v$open_cursor
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 SADDR						    RAW(4)
 SID						    NUMBER
 USER_NAME					    VARCHAR2(30)
 ADDRESS					    RAW(4)
 HASH_VALUE					    NUMBER
 SQL_ID 					    VARCHAR2(13)
 SQL_TEXT					    VARCHAR2(60)

--total cursors open, by session
SELECT a.VALUE, s.username, s.SID, s.serial#
  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';

SELECT   SUM (a.VALUE) total_cur, AVG (a.VALUE) avg_cur, MAX (a.VALUE)
                                                                      max_cur,
         s.username, s.machine
    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'
GROUP BY s.username, s.machine
ORDER BY 1 DESC;

SELECT   MAX (a.VALUE) AS highest_open_cur, p.VALUE AS max_open_cur
    FROM v$sesstat a, v$statname b, v$parameter p
   WHERE a.statistic# = b.statistic#
     AND b.NAME = 'opened cursors current'
     AND p.NAME = 'open_cursors'
GROUP BY p.VALUE;

SELECT 'session_cached_cursors' parameter, LPAD (VALUE, 5) VALUE,
       DECODE (VALUE,
               0, '  n/a',
               TO_CHAR (100 * used / VALUE, '990') || '%'
              ) USAGE
  FROM (SELECT MAX (s.VALUE) used
          FROM v$statname n, v$sesstat s
         WHERE n.NAME = 'session cursor cache count'
           AND s.statistic# = n.statistic#),
       (SELECT VALUE
          FROM v$parameter
         WHERE NAME = 'session_cached_cursors')
UNION ALL
SELECT 'open_cursors', LPAD (VALUE, 5),
       TO_CHAR (100 * used / VALUE, '990') || '%'
  FROM (SELECT   MAX (SUM (s.VALUE)) used
            FROM v$statname n, v$sesstat s
           WHERE n.NAME IN
                     ('opened cursors current', 'session cursor cache count')
             AND s.statistic# = n.statistic#
        GROUP BY s.SID),
       (SELECT VALUE
          FROM v$parameter
         WHERE NAME = 'open_cursors')
/