Open Cursors Analysis (vopencur.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script displays open cursors in the database, allowing you to:

• Find all open cursors for a specific session
• Search for cursors containing specific SQL text
• Diagnose cursor leak issues
• Monitor cursor usage patterns

The Script

rem  vopencur.sql
rem
ttitle 'Open Cursors'
rem
col sid format 9999 heading 'SID'
col sql_text format a60 heading 'SQL TEXT'
rem
select sid, sql_text
  from v$open_cursor
 where upper(sql_text) like upper('&text')
   and sid like '&sid'
/

Usage

-- Basic usage
@vopencur.sql

-- When prompted, enter:
-- text: SQL text pattern (use % for wildcard)
-- sid: Session ID (use % for all sessions)

Parameters

The script prompts for:

• &text - SQL text pattern to search for (use % for wildcard)
• &sid - Session ID to filter (use % for all sessions)

Required Privileges

SELECT ON V$OPEN_CURSOR

Sample Output

Open Cursors

 SID SQL TEXT
---- ------------------------------------------------------------
 125 SELECT * FROM employees WHERE department_id = :1
 125 SELECT COUNT(*) FROM orders WHERE order_date > :1
 248 BEGIN dbms_stats.gather_table_stats(:1, :2); END;
 248 SELECT /*+ INDEX(e EMP_IDX) */ * FROM employees e
 367 INSERT INTO audit_log (user_id, action, timestamp) VALUES (

Key Output Columns

• SID - Session ID holding the open cursor
• SQL TEXT - First 60 characters of the SQL statement

Common Use Cases

Diagnose Cursor Leaks

-- Find sessions with many open cursors
@vopencur.sql
-- Enter: % for text
-- Enter: 125 for sid (specific session)
-- Count results to see cursor usage

Find Specific SQL Patterns

-- Look for specific table access
@vopencur.sql
-- Enter: %EMPLOYEES% for text
-- Enter: % for sid

Monitor Application Cursors

-- Check cursors from specific application
@vopencur.sql
-- Enter: %APP_SCHEMA.% for text
-- Enter: % for sid

Cursor Management Best Practices

Cursor Limits:

-- Check current cursor limit
SHOW PARAMETER open_cursors

-- Check cursor usage statistics
SELECT name, value
FROM v$sysstat
WHERE name LIKE '%cursor%';

Common Issues:

1. Cursor leaks - Application not closing cursors properly
2. High parse rates - Not reusing cursors effectively
3. ORA-01000 - Maximum open cursors exceeded

Prevention:

1. Always close cursors in application code
2. Use connection pooling with proper cursor management
3. Monitor cursor cache hit ratio
4. Set appropriate OPEN_CURSORS parameter

Additional Analysis

-- Count open cursors per session
SELECT sid, COUNT(*) cursor_count
FROM v$open_cursor
GROUP BY sid
ORDER BY cursor_count DESC;

-- Find sessions approaching cursor limit
SELECT s.sid, s.username, s.program,
       COUNT(*) open_cursors,
       p.value max_cursors
FROM v$open_cursor oc, v$session s, v$parameter p
WHERE oc.sid = s.sid
AND p.name = 'open_cursors'
GROUP BY s.sid, s.username, s.program, p.value
HAVING COUNT(*) > p.value * 0.8
ORDER BY open_cursors DESC;

Related Scripts

• Active Sessions - View session details
• SQL by Session ID - Current SQL for sessions
• Top SQL Statements - Resource-intensive SQL