Search notes:

Oracle: V$OPEN_CURSOR

v$open_cursor shows cursors (of all sessions) that are currently opened and parsed, or cached.

select
--count(*),
  cursor_type
from
  v$open_cursor
group by
  cursor_type
order by
  cursor_type;
-- BUNDLE DICTIONARY LOOKUP CACHED
-- DICTIONARY LOOKUP CURSOR CACHED
-- OPEN
-- OPEN-PL/SQL
-- OPEN-RECURSIVE
-- PL/SQL CURSOR CACHED
-- SESSION CURSOR CACHED

Github repository oracle-patterns, path: /Installed/dynamic-performance-views/open_cursor/cursor_types.sql

select
  opc.user_name,
--mon.username,
--mon.module,
  opc.sql_id,
  opc.sql_exec_id,
--mon.status,
  opc.sql_text,
  opc.last_sql_active_time,
  opc.cursor_type
from
  v$session        ses                                       join
  v$open_cursor    opc on ses.sid          = opc.sid /* left join
  v$sql_monitor    mon on opc.sql_exec_id  = mon.key */
where
  ses.audsid = sys_context('userenv', 'sessionid')
order by
  decode (opc.user_name, 'SYS', 1, 0);

Github repository oracle-patterns, path: /Installed/dynamic-performance-views/open_cursor/own-sessions-open-cursors.sql

Oracle: V$OPEN_CURSOR

See also