Open_Cursors parameter in Oracle

The total allowable open cursors per session is dependent upon and should not exceed open_cursors db initialization parameter setting. The value for this init parameter can be inquired, by using:

SELECT value FROM v$parameter WHERE name = ‘open_cursors’;

Here are two scripts that you can use to see whether you have cursor leaks in your system and whether additional information pertaining to those cursors:

/****************************************************************************************
SQL 1: Shows the Program name and sqltext for open cursors in active sessions.
–       It will not show information about the session you are currently logged
–       in though – if you want that, comment out the line mentioned in the code below.
–       This will output to the file Program_Cursor_SQL.txt in the directory
–       this script is run from.
*****************************************************************************************/

SPOOL Program_Cursor_SQL.txt
SET linesize 200 pages 100 feed ON verify OFF echo OFF
COLUMN Program   heading ‘Program’ format a48 wrap
COLUMN SQL       heading ‘SQL|Text’    format a64 wrap
break ON Program skip 2
SELECT /*+rule */
ses.Program AS Program,
sqltxt.sql_text SQL
FROM  v$open_cursor ocur,
v$session ses,
v$sqltext sqltxt
WHERE ocur.sid = ses.sid
AND ses.Status = ‘ACTIVE’            — Only active sessions.
AND ses.username = ????              — Use ses.username IS NOT NULL for all users.
AND ((ses.sql_address = sqltxt.ADDRESS AND  ses.sql_hash_value = sqltxt.hash_value)
OR
(ses.prev_sql_addr  = sqltxt.ADDRESS AND  ses.prev_hash_value = sqltxt.hash_value))
AND ocur.ADDRESS= sqltxt.ADDRESS AND  ocur.hash_value = sqltxt.hash_value
/*Don’t include this session (comment out if you wish to include your session)*/
AND ses.sid != (SELECT sid FROM v$session WHERE audsid=USERENV(’sessionid’))
ORDER BY ses.sid, ses.serial#,ses.process, ses.username,ses.program,sqltxt.piece
/
clear breaks
clear COLUMNS
SPOOL OFF

/****************************************************************************************
– SQL 2: This script is used to report the total number of open cursors for
–        the currently open user sessions (active/inactive) in the database
–        by USERNAME.
*****************************************************************************************/

SET pages 1000 lines 200 feed OFF echo OFF
COLUMN username    heading ‘DB User’ format a30
COLUMN act_sess    heading ‘Active|Sessions’ format 9,999
COLUMN inact_sess  heading ‘Inactive|Sessions’ format 9,999
COLUMN tot_sess    heading ‘Total|Sessions’ format 99,999
COLUMN act_curs    heading ‘Active|Cursors’ format 999,999
COLUMN inact_curs  heading ‘Inactive|Cursors’ format 999,999
COLUMN tot_curs    heading ‘Total|Cursors’ format 9,999,999
break ON report
compute SUM label ‘Total Count:’ OF tot_sess tot_curs ON report
SELECT username,
act_sess,
inact_sess,
(act_sess+inact_sess) AS tot_sess,
act_curs,
inact_curs,
(act_curs+inact_curs) AS tot_curs
FROM
(SELECT s.username,
(SELECT COUNT(*) FROM v$session ss WHERE ss.username = s.username AND ss.status = ‘ACTIVE’) AS act_sess,
(SELECT COUNT(*) FROM v$session ss WHERE ss.username = s.username AND ss.status = ‘INACTIVE’) AS inact_sess,
COUNT(DECODE(s.status, ‘ACTIVE’, oc.sid)) AS act_curs,
COUNT(DECODE(s.status, ‘INACTIVE’, oc.sid)) AS inact_curs
FROM v$open_cursor oc inner join v$session s ON oc.sid = s.sid
WHERE s.username NOT IN (‘SYS’, ‘SYSTEM’)
GROUP BY s.username)
ORDER BY tot_curs DESC
/
clear breaks
clear COLUMNS