Oracle cursors parameters

To view open cursors on oracle

No of Open cursors on Oracle(Admin permission is required)
select count(*) from v$open_cursor;

To see open_cursors parameter on oracle db.
show parameter open_cursors;

To set open_cursors parameter on oracle db.

alter system set open_cursors=700 scope=memory;

To see the sql query associated with cursor

select sid, sql_text from v$Open_cursor order by sid, sql_text;

To select number of open cursors, machine name, operating system and user name executing the query, run the following

SELECT v.value as numopencursors ,s.machine ,s.osuser,s.username
FROM V$SESSTAT v, V$SESSION s
WHERE v.statistic# = 3 and v.sid = s.sid ;

Sphere: Related Content