Skip to content
Oracle DBA Scripts & Database Utilities

Open Cursor Analysis (vopencur.sql)

What This Script Does

Section titled “What This Script Does”

This script helps analyze open cursors in the database by:

  • Showing SQL statements currently held open by sessions
  • Filtering by SQL text pattern to find specific queries
  • Filtering by session ID to focus on particular sessions
  • Helping diagnose cursor leak issues

Script

Section titled “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

Section titled “Usage”
SQL> @vopencur.sql
Enter value for text: %EMPLOYEE%
Enter value for sid: %

Parameters

Section titled “Parameters”
  • text: SQL text pattern to search for (use % for wildcard)
  • sid: Session ID to filter (use % for all sessions)

Required Privileges

Section titled “Required Privileges”
  • SELECT on V$OPEN_CURSOR

Sample Output

Section titled “Sample Output”
Open Cursors


 SID SQL TEXT
---- ------------------------------------------------------------
 156 SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME FROM EMPLOYEES
 156 UPDATE EMPLOYEES SET SALARY = :1 WHERE EMPLOYEE_ID = :2
 234 SELECT COUNT(*) FROM EMPLOYEES WHERE DEPARTMENT_ID = :1
 234 SELECT E.*, D.DEPARTMENT_NAME FROM EMPLOYEES E, DEPARTMENTS D
 512 DELETE FROM EMPLOYEES WHERE TERMINATION_DATE < :1

Key Output Columns

Section titled “Key Output Columns”
  • SID: Session identifier holding the cursor
  • SQL TEXT: First 60 characters of the SQL statement

Understanding Open Cursors

Section titled “Understanding Open Cursors”

Cursor Basics

Section titled “Cursor Basics”
  • Cursors are memory structures for SQL execution
  • Each parsed SQL statement requires a cursor
  • Cursors remain open until explicitly closed

Common Issues

Section titled “Common Issues”
  1. Cursor Leaks: Application not closing cursors
  2. High Usage: Too many unique SQL statements
  3. Limit Exceeded: ORA-01000 maximum open cursors exceeded

Monitoring Guidelines

Section titled “Monitoring Guidelines”
  • Normal range: 50-300 cursors per session
  • Warning level: >500 cursors per session
  • Critical: Approaching OPEN_CURSORS parameter limit

Common Use Cases

Section titled “Common Use Cases”

  1. Troubleshooting ORA-01000

    -- Find sessions with most open cursors
    SELECT sid, COUNT(*) cursor_count
    FROM v$open_cursor
    GROUP BY sid
    ORDER BY 2 DESC;

  2. Application Analysis

    • Identify cursor leak patterns
    • Find non-parameterized queries
    • Validate cursor management

  3. Performance Tuning

    • Detect excessive parsing
    • Find candidates for bind variables
    • Optimize cursor sharing

Best Practices

Section titled “Best Practices”

  1. Application Design

    • Always close cursors when done
    • Use bind variables to reduce cursors
    • Implement proper error handling

  2. Database Configuration

    • Set OPEN_CURSORS appropriately
    • Monitor cursor cache hit ratio
    • Enable cursor sharing if needed

  3. Monitoring

    • Regular checks for high cursor counts
    • Alert on approaching limits
    • Track cursor usage trends
Section titled “Related Scripts”