Error ORA-01000: maximumopencursorsexceededException

ORA-01000 is a common exception in development. This exception indicates the number of opened cursors in the program. gt; the maximum number of opened cursors set in the database.

ORA-01000 is a common exception in development. This exception indicates the number of opened cursors in the program. gt; the maximum number of opened cursors set in the database.

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 ){}

}

Related reading:

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