view open cursor usage in Oracle

--count open cursors by username, SID
break on report
comp sum of curs on report
select user_name, SID, count(*) cursors from V$OPEN_CURSOR group by User_Name, SID order by User_Name, SID;

--displays information on cursor usage for the current session
select * from V$SESSION_CURSOR_CACHE;

--displays information on cursor usage for the system.
select * from V$SYSTEM_CURSOR_CACHE;

--displays open_cursors usage in details
select user_name,to_char(sysdate,'hh24:mi:ss') col1, sql_text from V$OPEN_CURSOR