The maximum number of open cursors in an oracle database can be determined by the following query
select name, value
from v$parameter
where name like ‘open-cursors’
the value for open_cursors parameter refers to maximum number of open cursors allowed.
The below query can be used to determine number of open cursors for the current session.
SELECT
sid,user_name, COUNT(*) "Cursors per session"
FROM v$open_cursor
GROUP BY sid,user_name