Incorrect ORA-01000: maximum open cursors exceeded Exception

ORA-01000 is a common exception in development. This exception indicates the number of opened cursors in the program> the maximum number of opened cursors set in the database.
 
This exception is usually caused by two reasons.
 
1. The maximum open cursors set in the database is too small
 
2. In Java JDBC programs, the ResultSet or PreparedStatment opened in the Code is not closed.
 
What is a cursor?
 
A cursor is a pointer to a result set. It can be regarded as a resource or a data structure.
 
 
View the maximum number of allowed cursors in the system and the maximum number of opened cursors in history
 
Select max (a. value) as highest_open_cur, p. value as max_open_cur
From v $ sesstat a, v $ statname B, v $ parameter p
Where a. statistic # = B. statistic # and B. name = 'opened cursors current' and p. name = 'open _ cursors'
Group by p. value;
 

HIGHEST_OPEN_CUR MAX_OPEN_CUR
 
----------------------------
 
69 300
 
 
From the results, we can see that the maximum number of cursors allowed by the system is 300, and the maximum number of opened cursors in history is 30. We can conclude that 300 is enough. If the two numbers are very close or equal, you need to modify the maximum number of allowed cursors.
 
View who is using the cursor

Select sum (a. value) total_cur, avg (a. value) avg_cur,
 
Max (a. value) max_cur, s. username, s. machine
 
From v $ sesstat a, v $ statname B, v $ session s
 
Where a. statistic # = B. statistic # and s. sid = a. sid
 
And B. name = 'opened cursors current'
 
Group by s. username, s. machine order by 1 desc
 
 
 

TOTAL_CUR AVG_CUR MAX_CUR USERNAME MACHINE
 
--------------------------------------
 
114 14.25 69 A KUL-0121
 
76 8.44 48 B KUL-0121
 
29 1.38 18 KUL-0121
26 8.67 13 C xxx \ KUL-012
 
 
 
Since we can know the applicability of the cursor from the system, we need to fix this exception below.
 
1. By setting OPEN_CURSORS, MAX_VALUE increases the maximum number of cursors.

Alter system set OPEN_CURSORS = MAX_VALUE SID = '*' SCOPE = BOTH;
 
 
2. This exception is usually caused by the fact that the ResultSet or PreparedStatment is not closed by the Java program. If the correct method is used, close () is always placed in finally. In this way, ResultSet and PreparedStatement are disabled no matter whether other exceptions occur.

Statement stmt = conn. createStatement ();
 
Try {
 
ResultSet rs = stmt.exe cuteQuery ("SELECT FULL_NAME from emp ");
 
Try {
 
While (rs. next ()){
 
System. out. println ("Name:" + rs. getString ("FULL_NAME "));
 
}
 
} Finally {
 
Try {rs. close ();} catch (Exception ignore ){}
 
}
 
} Finally {
 
Try {stmt. close ();} catch (Exception ignore ){}
 
}

About ORA-01000: maximum open cursors exceeded problem analysis summary

Questions about Oracle cursors (ORA-01000: maximum open cursors exceeded)

Oracle ORA-01000: maximum open cursors exceeded

ORA-01000: maximum open cursors exceeded