 Posted 2/27/2009 2:36:01 PM
|
|
|
|
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.
What this means is that you have too many database connections. It also means that you as a DBA cannot connect internal to do something about it! When you try to connect to 'sys as sysdba' it gets you to the SQL prompt but you won't be able to do anything.
Here's a couple of things you can do to fix this:
* You need to ask a couple of users to log out of the database, or
* You need to kill some sessions at the OS level.
Once you managed to connect after some sessions have been cleared, see if you can spot anything unordinary making abnormal numbers of connections. If you can't find anything directly you'll need to increase the init parameter 'processes'. Unfortunately, this will require a restart of the database to take effect in some cases where spfile is not used.
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 100
SQL> select count(*) from v$process;
COUNT(*)
----------
94
SQL> alter system set processes=500 scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 500
In my case I had 94 processes which about 5 of them got cleaned up which allowed me to login. The maximum processes alllowed in the db is 1 - the max init parameter called processes. So if you set your parameter to be 100 then max that can login is 99.
When you reach this you can occasionally get a TNS ora error connecting via TNS when the database is out of processes:
ORA-12516: TNS:LISTENER could not find available handler with matching protocol stack
This is another indication that possible max processes have been reached.
|
|
|
|