My Oracle Support Banner

ORA-01000 : Troubleshooting Open Cursors Issues (Doc ID 1477783.1)

Last updated on AUGUST 30, 2023

Applies to:

Identity Manager - Version 11.1.1.3.0 and later
Oracle Database - Enterprise Edition - Version 11.2.0.4 to 11.2.0.4 [Release 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

Purpose

Every application using oracle database as backend repository runs several SQL statements.

For every SQL statement execution in oracle database, certain area in memory is allocated. Oracle PL/SQL allows you to name this area. This private SQL area is called context area or cursor. These cursors take up space in the shared pool (essential memory component of oracle database), specifically in the library cache. To keep a renegade session from filling up the library cache, or clogging the CPU with millions of parse requests, we set the parameter OPEN_CURSORS.

One defines the value/limit of these cursors as a DB parameter called OPEN_CUSRSORS in database instance.

OPEN_CURSORS DB parameter sets the maximum number of cursors each session can have open, per session. For example, if OPEN_CURSORS value is set to 1000, then each session can have up to 1000 cursors open at one time.

Many times we face issues due to maxing out of these cursors in our database because of connection leakage from application code as below:

 

Troubleshooting Steps

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
Troubleshooting Steps

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.