Oracle Parameter Issue (OPEN_CURSORS)

 

Problem

A production application suddenly starts throwing errors like:

ORA-01000: maximum open cursors exceeded

Users report:

• Application pages failing

• Transactions not completing

• Database sessions getting stuck

This issue is related to the Oracle Database initialization parameter OPEN_CURSORS.

---

📌 What is OPEN_CURSORS

OPEN_CURSORS defines the maximum number of cursors a session can have open simultaneously.

Default value in many systems:

OPEN_CURSORS = 300

In high-transaction applications this can be too low.

---

🔎 Investigation Steps (Real DBA Approach)

1️⃣ Check current parameter value

show parameter open_cursors;

Example result

NAME           TYPE        VALUE
-------------- ----------- -----
open_cursors   integer     300

---

2️⃣ Identify sessions using many cursors

SELECT sid,
       COUNT(*) "OPEN CURSORS"
FROM v$open_cursor
GROUP BY sid
ORDER BY 2 DESC;

This helps find sessions consuming too many cursors.

---

3️⃣ Check which SQLs are open

SELECT sid, sql_id, COUNT(*)
FROM v$open_cursor
GROUP BY sid, sql_id
ORDER BY 3 DESC;

Sometimes the issue is caused by application cursor leaks.

---

🛠 Solution

Increase the parameter value.

ALTER SYSTEM SET open_cursors=1000 SCOPE=BOTH;

Explanation:

• SCOPE=BOTH updates

  • running instance

  • SPFILE parameter file

Verify again:

show parameter open_cursors;

---

⚠️ Important DBA Check

Before increasing the value, verify memory usage and application behavior.

Also check:

SELECT COUNT(*) FROM v$open_cursor;

If the application is not closing cursors, developers must fix the code.

---

📊 Root Cause (Real Production Case)

In one real project:

• A Java application using Oracle WebLogic Server

• JDBC connections were not closing cursors properly

• Cursor count kept increasing

• Database hit OPEN_CURSORS limit

---

✅ Final Fix Implemented

1. Increased parameter

open_cursors = 1500

2. Development team fixed cursor closing in code

3. Monitored sessions for 48 hours.

System became stable.

---

📘 Key Learning for Oracle DBAs

Always check before changing parameters:

• v$parameter

• v$open_cursor

• v$session

• v$sql

Never increase parameters blindly.