This content has been marked as final. Show 15 replies
Why would it be a problem?
Did your user community grow?
Or do you think for every problem
1 Oracle is too blame
2 I can't do any research, so let's dump it in an OTN forum
3 My boss beats me up for every character I type here, so I'll post an useless question without platform and version info, and sit back with my feet on my desk, and let those poor volunteers sweat.
Senior Oracle DBA
check the number of sessions connected to the database.
if the connections exceeded or equal to the process parameter,
1.Edit the database init.ora file
2. Locate and increase the processes parameter to a higher value.
3. Save the init.ora file
4. Stop and restart the database
good start would be to start looking at v$process and v$session...see how many session where connected back then and how many session where connected when the error came to u....it might be just that ...you got more connections that you anticipated.
$ oerr ora 00020
00020, 00000, "maximum number of processes (%s) exceeded"
// *Cause: All process state objects are in use.
// *Action: Increase the value of the PROCESSES initialization parameter.
check the entry in the Oracle version# Reference manual for the meaning of PROCESSES.
I would guess that over time the load on the database has increased and you have now hit the limit, PQO has been put into use and there were a lot of PQO sessions, and/or you had a fair number of run-a-way processes on your system.
One or a combination of these three items probably accounts for why you ran out of Oracle processes.
HTH -- Mark D Powell --
You also need to increase value for sessions and transaction parameters as well.
Here is rule as suggested by oracle
processes=x sessions=x*1.1+5 transactions=sessions+1.1 E.g. processes=500 sessions=555 transactions=610
its due to database exhausted with unneeded processes. checking the OS processes and killing them would help.
how is it related to sessions parameter? i know setting process parameter(as you mentioned) would help.
just to ask.. one thing
can anyone tell me
if processes exceed to ita maximum threshold
then how to login into database
it would not alow even with "as sysdba"
then what to do
how to find the inactive sessions which are oldest one
but how to find these sesions w/o login in database
may be need to kill one session ..so one connection can be established to database?
u will have to go thru OS to kill that session...i would say look into TOP (if u on unix) and try and find a idel session...
you mean to say all those (atleast most of) idle processes in top (or glance) .. will be ..or may be ..or must be InActive session in database.
Here read thru this link
Thanks for the link.
yes try to look for a process in top or glance and see how long it been "idle" ....and make sure its a oracle process that you can kill...and ask one of the users to log off...so the sysdba can login....
In addition make sure there are no additional errors reported in alert.log and at times I have experienced listner process exits or simply died. So focus on application, use of connection pooling and set out profile to timeout session sitting idle for more than 30 or 60 mins or whatever appropriate. Also at application side, make sure its closing connections appropriately or reusing it and not just keep opening up new connections. But you do need to factor future growth (application users) when you come up with values of these parameters.
Thanks .. nice article ..