ORA-01000: analysis and resolution of issues that exceed the maximum number of opened cursors

Symptom: system running for a while error: Java. SQL. sqlexception: ORA-01000: exceeds the maximum number of opened cursors

Step 1:
View the current number of cursors of the database. Configure slqplus: Show parameter open_cursors;

Step 2:
View cursor usage:
Select O. Sid, osuser, machine, count (*) num_curs
From v $ open_cursor o, V $ session s
Where user_name = 'USER' and O. Sid = S. Sid
Group by O. Sid, osuser, Machine
Order by num_curs DESC;
In user_name = 'user', user indicates the username of the database that occupies database resources.

Step 3:
View the SQL statement executed by the cursor:

Select O. Sid Q. SQL _text
From v $ open_cursor o, V $ SQL Q
Where Q. hash_value = O. hash_value and O. Sid = 123;

Step 4:
analyze whether the Program accessing the database is normal when resources are released, if the program releases resources, increase the number of cursors.
alter system set open_cursors = 2000 scope = both;
supplement: in Java Code , run Conn. createstatement () and conn. when preparestatement () is used, it is equivalent to opening a cursor in the database. In particular, if your createstatement and preparestatement are in a loop, this issue is very likely to occur. Because the cursor is always open and not closed.
generally, when writing Java code, both createstatement and preparestatement should be placed outside the loop and closed in time after these statment are used. It is best to immediately close statment and call the close () method if you do not need to use resultset data after executing a executequery or executeupdate operation.