Code:
SELECT 
   s.sid, 
   s.username, 
   s.program, 
   count(oc.sid) open_cursors
FROM 
   v$session s, 
   v$open_cursor oc
WHERE
   oc.sid = s.sid
GROUP BY
   s.sid, 
   s.username, 
   s.program
ORDER BY
   open_cursors desc