How to check open cursors per session in Oracle

Cursor leaks surface as ORA-01000 (maximum open cursors exceeded). This ranks sessions by open cursor count and shows how close each is to the OPEN_CURSORS parameter.

SELECT o.sid, s.username, s.machine, s.program,
       COUNT(*) AS open_cursors,
       (SELECT value FROM v$parameter WHERE name = 'open_cursors') AS "LIMIT"
FROM   v$open_cursor o
JOIN   v$session     s ON s.sid = o.sid
GROUP BY o.sid, s.username, s.machine, s.program
ORDER BY open_cursors DESC;