none
ORA - 00020 Maximum number of processes

    Question

  • Hello All,

    I have a Visual Studio 2005 project, using VB 2005 and Oracle as my database. After rolling out the program to several user machines, they are getting the "ORA - 00020 Maximum number of processes (%s) exceeded." error.

    It seems, 3 users are okay but when a 4th tries to use it, the error occurs.

    Can I be causing this in my app or is it something to be tuned in Oracle?

    I have about 50 datasets defines in the app, but not left open.

     

    This has reached the critical stage - any ideas?

    Thanks VERY much,
    Carl

    Monday, June 18, 2007 4:00 PM

Answers

  • This is an Oracle configuration setting.  You should first try setting "Max Pool Size=5" in your Oracle connection string to see if it is caused by your client application not properly closing connections.  If you don't properly close all connections when you are through with them they will pile up in the connection pool.  Fix the pooling problem and your ORA-00020 should go away most likely.  If not, then you need to talk to your Oracle DBA and mention ORA-00020 and he or she should know what to do on the server side.
    Monday, June 18, 2007 4:22 PM

All replies

  • This is an Oracle configuration setting.  You should first try setting "Max Pool Size=5" in your Oracle connection string to see if it is caused by your client application not properly closing connections.  If you don't properly close all connections when you are through with them they will pile up in the connection pool.  Fix the pooling problem and your ORA-00020 should go away most likely.  If not, then you need to talk to your Oracle DBA and mention ORA-00020 and he or she should know what to do on the server side.
    Monday, June 18, 2007 4:22 PM
  • Thanks for the information. 

     

    I have made code changes where there might be connections left open and lowered the connection pool to 5 and I can run one copy just fine.

     

    What I really need is to be able to monitor how many connections are open.  How is this done in VB code?

     

    Thanks,

    Carl

     

    Tuesday, June 19, 2007 3:08 PM
  • Unfortunately there is no way to monitor the number of connections in the pool in .NET short of doing some major reflection hacks.  If you have access to your Oracle server you can run:

     

    SELECT * FROM V$SESSION WHERE PROGRAM IS NOT NULL

     

    To get list of active connections.  You can also look at the .NET CLR Data performance counters but I have heard that these are not very reliable, see -> http://msdn2.microsoft.com/en-us/library/aa175863(SQL.80).aspx and also http://blogs.msdn.com/angelsb/archive/2004/08/02/206470.aspx

     

     

     

     

    Tuesday, June 19, 2007 6:27 PM
  • Thanks for the help.  I have found my "connection leak" and I think it's fine now.

     

    Thanks again,

    Carl

     

    Tuesday, June 19, 2007 8:07 PM
  • Setting Max Pool Size=5" was most helpful, thanks!
    Wednesday, September 21, 2011 6:43 PM