About ORA-00020: Maximum number of processes (150) exceeded Solution

Many friends in Java Development, when using Oracle database, often encounter a ORA-01000: maximum open cursors exceeded. Error.
In fact, the cause of this error is mainly caused by code problems.
Ora-01000: maximum open cursors exceeded.
Indicates that the maximum number of opened cursors has been reached for a process.
The main cause of this error is that the Java code is executing 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.
In general, 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 disable statment immediately after executing executequery, executeupdate, and so on without using the resultset data.
For the case of ORA-01000 errors, simply increase open_cursors is not a good way, it is just a temporary cure. In fact, the hidden risks in the Code have not been removed.
In most cases, open_cursors only needs to set a small value, which is enough unless there are special requirements.
---
If you do not use the connection pool, there is no problem. Once the connection is closed, the physical connection of the database is released, and all related Java resources can be recycled by GC.
However, if you use a connection pool, please note that the connection is not physically closed, but the connection pool is returned. Therefore, both preparedstatement and resultset are held and actually occupy the cursor resources of the relevant database, in this case, as long as the program runs for a long time, the error "the cursor exceeds the maximum allowed value of the Database" is often reported, causing the program to fail to access the database normally.
---
This is not related to the use of the conn pool. The general operation is like this. The thread obtains a conn from the outside, creates its own stmt and RS, and then performs logical operations, then return the conn to the pool. If the programmer forgets to manually shut down the location. After this thread is executed, stmt and RS are all junk. When they are collected by garbage collection, GC will shut them down for us. This means that many codes are still running normally without being closed.
But there is a potential problem. GC cannot determine when to run. If there is a lot of free memory, it is very likely that some GC will not be started, so stmt will not be shut down for a while, and an error will be reported during execution.
Therefore, robust code should be manually disabled for both RS and stmt
---
Java makes mistakes when connecting Oracle
1. Only understand createstatement, not close statement
2 .. Only createstatement and preparedstatement are supported.
3. Only use to_date in SQL, or even use string directly. Do not use setdate ()
---
I remember this problem also occurred in my program,
The main reason is that after I get the connection object, this connectin is not closed,
This problem also occurs if the preparedstatement object is not closed,
In addition, we recommend that you use local variables as much as possible for these database operation variables. Currently, the local variables are used and can be closed at any time and placed in finally {}. This is more secure.
---
Generally, this is because you have used the number of connections that are available at the same time over the DB settings.
You can refer to the following methods:
1. If you use the connection pool, you can set the max connection of the Connection Pool
Reduce the number of data to see if there is no connection at the same time
2. If you have not used the conneciton pool, check the connection.
Does it matter?
Another situation is that your system usage is really large, so 150 of the DB connection at the same time
The session is not closed, so you need to merge the entire Oracle database

Many friends in Java Development, when using Oracle database, often encounter a ORA-01000: maximum open cursors exceeded. Error. In fact, the cause of this error is mainly caused by code problems. Ora-01000: maximum open cursors exceeded. indicates that the maximum number of cursors opened by a process has been reached. The main cause of this error is that the Java code is executing 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. In general, 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 disable statment immediately after executing executequery, executeupdate, and so on without using the resultset data. For the case of ORA-01000 errors, simply increase open_cursors is not a good way, it is just a temporary cure. In fact, the hidden risks in the Code have not been removed. In most cases, open_cursors only needs to set a small value, which is enough unless there are special requirements. --- If you do not use the connection pool, there is no problem. Once the connection is closed, the physical connection of the database is released, and all related Java resources can be recycled by GC. However, if you use a connection pool, please note that the connection is not physically closed, but the connection pool is returned. Therefore, both preparedstatement and resultset are held and actually occupy the cursor resources of the relevant database, in this case, as long as the program runs for a long time, the error "the cursor exceeds the maximum allowed value of the Database" is often reported, causing the program to fail to access the database normally. --- This is not related to the use of the conn pool. The general operation is like this. The thread obtains a conn from the outside, creates its own stmt and RS, and then performs logical operations, then return the conn to the pool. If the programmer forgets to manually shut down the location. After this thread is executed, stmt and RS are all junk. When they are collected by garbage collection, GC will shut them down for us. This means that many codes are still running normally without being closed. But there is a potential problem. GC cannot determine when to run. If there is a lot of free memory, it is very likely that some GC will not be started, so stmt will not be shut down for a while, and an error will be reported during execution. Therefore, robust code should be used to manually close RS and stmt --- Java connection to Oracle often makes mistake 1. I only understand createstatement, but I don't know how to close Statement 2 .. Only createstatement, not preparedstatement. 3. Only use to_date in SQL, or even use string directly. Do not use setdate () --- I remember. this problem also exists in my program. The main reason is that after I get the connection object, this connectin is not closed, and the preparedstatement object is also generated at the same time, in addition, we recommend that you use local variables as much as possible for these database operation variables. Currently, the local variables are used and can be closed at any time and placed in finally, relatively safe-generally, this is the case where you have used the number of connections available at the same time over the DB settings. You can refer to the following methods for more information: 1. if you use the connection pool, you can reduce the max connection number of the connection pool. At the same time, check whether there are connections. 2. if conneciton pool is not used, check whether your connection has been used. Another situation is: the usage of your system is really large, so 150 of DB connection sessions at the same time are not interrupted, You need to merge the entire oracle db