ORA - 00020 Maximum number

[carlp22]

Hello,

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

 

[cjard]

I think youre hitting the licensed limit of your oracle server, in terms of allowed concurrent connections.

You cant leave a dataset open; there isnt the concept of open and closed with a dataset any more than an array can be "open" or "closed"

 

[carlp22]

Connection open?

I might be leaving connections open. Is there a way to monitor how many connections are open in my VB code?

Thanks,
Carl

 

[cjard]

It is. It's called COnnection Pooling and turning it off is a seriously bad move for performance. Only do it if you really know what youre doing. Changing the licensing model would be better.

 

[carlp22]

More info

According to the Oracle DBA, he can max out the connections at 300 (it's about 250 now). With about 10 copies of my app running, we hit the max.

I'm still going through the code looking for open connections. What would be REALLY useful is some code I can insert into the VB app to show me the number of open connection.

Thanks,
Carl

 

[cjard]

Select * From V$session ..

 

[cjard]

I'm still going through the code looking for open connections.

You wont find them. Read up about .NET connection pooling

 

[carlp22]

V$session layout

jcard,

Thanks for the reply. I think I had a problem in a subroutine returning a datareader.

I put in the SQL you suggested. I've been searching for the layout of what's being returned, but no luck. I see I get 47 fields, but I don't know what they are!

Thanks again for all the help you provide.
Carl

 

[cjard]

v$session shows you active sessions, who owns them, what exe they are etc.. useful stuff.. Use it to determine just how many connections your app makes. Most my apps keep a pool of 2 to 3.. Even then, only when they are actively using the database.. The connections disappear after a while