[Solved] ORA-12519, TNS:no appropriate service handler found

Unpredictably, one instance of your program gives the exception below, while most of the time the program works just fine.

java.sql.SQLException: Listener refused the connection with the following error:

ORA-12519, TNS:no appropriate service handler found

                at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:673)

                at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:715)

                at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:385)

                at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:30)

                at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:564)

                at java.sql.DriverManager.getConnection(Unknown Source)

                at java.sql.DriverManager.getConnection(Unknown Source)

This exception is usually caused by that the number of database connections exceeded the number of connections the listener can handle. Check through your code to make sure that all the open connections are necessary and all of them are closed after their jobs are done. Especially, when you use a connection in a loop, make sure you don't create a connection in each iteration, unless you have to, then close it immediately once it is not needed.

Quick fixes to this problem:

1. Restart the database. Most of the time, this is sufficient to solve the problem.

2. If the problem keeps coming back after you tried solution 1 above, increase the database System processes so that the listener is set to handle more connections.

Log on to your database as the database administrator.

SQL>alter system set processes=300 scope=spfile;
SQL>alter system set open_cursors=500;
SQL>shutdown immediate
SQL>startup

3. If you do not want to increase the System processes, take the steps below to solve the problem.

SQL>select count(*) from v$process;

SQL>select count(*) from v$session;

If the count you get is close to 150, the default maximum number of processes allowed, run the following SQL to find out who is doing what.

SQL> SELECT sn.process, sn.status, sn.username, sn.schemaname, sql.sql_text

           FROM v$session sn, v$sql sql

           WHERE sql.sql_id(+) = sn.sql_id and sn.type='USER' and sql.elapsed_time is not null                       ORDER BY sql.elapsed_time desc;

---------------------------------------------------------------------------------------------------------------

                        
If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.