ORA-00020 maximum number of processes exceeded
Cause: All process state objects are in use.
Action: Increase the value of the PROCESSES initialization parameter.
ORA-00020 comes under "Oracle Database Server Messages". These messages are generated by the Oracle database server when running any Oracle program.
Reference: Oracle Documentation
How to increase PROCESSES initialization parameter:
1. Login as sysdba
sqlplus / as sysdba
2. Check Current Setting of Parameters
sql> show parameter sessions
sql> show parameter processes
sql> show parameter transactions
3. If you are planning to increase "PROCESSES" parameter you should also plan to increase "sessions and "transactions" parameters
A basic formula for determining these parameter values is as follows:
processes=x
sessions=x*1.1+5
transactions=sessions*1.1
4. These paramters can't be modified in memory. You have to modify the spfile only (scope=spfile) and bounce the instance.
sql> alter system set processes=500 scope=spfile;
sql> alter system set sessions=555 scope=spfile;
sql> alter system set transactions=610 scope=spfile;
sql> shutdown abort
sql> startup
Related Links:
- ORA-04031: unable to allocate n bytes of shared memory
- ORA-00018 maximum number of sessions exceeded
Cause: All process state objects are in use.
Action: Increase the value of the PROCESSES initialization parameter.
ORA-00020 comes under "Oracle Database Server Messages". These messages are generated by the Oracle database server when running any Oracle program.
Reference: Oracle Documentation
How to increase PROCESSES initialization parameter:
1. Login as sysdba
sqlplus / as sysdba
2. Check Current Setting of Parameters
sql> show parameter sessions
sql> show parameter processes
sql> show parameter transactions
3. If you are planning to increase "PROCESSES" parameter you should also plan to increase "sessions and "transactions" parameters
A basic formula for determining these parameter values is as follows:
processes=x
sessions=x*1.1+5
transactions=sessions*1.1
4. These paramters can't be modified in memory. You have to modify the spfile only (scope=spfile) and bounce the instance.
sql> alter system set processes=500 scope=spfile;
sql> alter system set sessions=555 scope=spfile;
sql> alter system set transactions=610 scope=spfile;
sql> shutdown abort
sql> startup
Related Links:
- ORA-04031: unable to allocate n bytes of shared memory
- ORA-00018 maximum number of sessions exceeded
nice error reference...
ReplyDeleteHello,
ReplyDeleteAfter Application outage processes have been growing and it has hit ORA-00020: maximum number of processes (1000) exceeded.
Now that we have increased the Process value from 1000 to 1200 issue is resolved.
issue is that ora-00020: maximum number of processes 1000 exceeded ,database output shows that query against the views V$SESSION and V$PROCESS show different results.
we know that There is an application running and connecting to the database . The connection against the database is failing.
How can i know which user & terminal is spawning these processs on database server side? what is the Best way to identifiy the Client source/machine related to these process ?
but only 70 sessions show in v$ and gv$session? In v$process 687, the used slots show a terminal of unknown, and program of unknown, like a zombie process. With out that information, I can not find out what is calling the database, and burning up all my processes.
I WANT to know the source Terminal/Client spawning these process (LOCAL=NO).
75D-XYZ]-oracle-> ps -ef |grep XYZ
oracle 25934 1 0 Sep 16 ? 0:01 oracleXYZ (LOCAL=NO)
oracle 20220 1 0 09:57:37 ? 0:01 oracleXYZ (LOCAL=NO)
oracle 22968 1 0 Sep 13 ? 0:01 oracleXYZ (LOCAL=NO)
oracle 23436 1 0 Sep 20 ? 0:00 oracleXYZ (LOCAL=NO)
oracle 21448 1 0 Sep 13 ? 0:00 oracleXYZ (LOCAL=NO)
oracle 7216 1 0 Sep 13 ? 0:00 oracleXYZ (LOCAL=NO)
oracle 10760 1 0 Sep 16 ? 0:00 oracleXYZ (LOCAL=NO)
oracle 8385 1 0 Sep 16 ? 0:01 oracleXYZ (LOCAL=NO)
oracle 22952 1 0 Sep 17 ? 0:00 oracleXYZ (LOCAL=NO)
oracle 10911 1 0 Sep 13 ? 0:00 oracleXYZ (LOCAL=NO)
oracle 14401 1 0 Sep 13 ? 0:00 oracleXYZ (LOCAL=NO)
oracle 6685 1 0 Sep 19 ? 0:01 oracleXYZ (LOCAL=NO)
oracle 7434 1 0 09:11:44 ? 0:00 oracleXYZ (LOCAL=NO)
oracle 13560 1 0 09:17:57 ? 0:00 oracleXYZ (LOCAL=NO)
oracle 7928 1 0 Sep 13 ? 0:00 oracleXYZ (LOCAL=NO)
oracle 3623 1 0 Sep 10 ? 0:54 ora_qmnc_XYZ
oracle 29210 1 0 Sep 13 ? 0:01 oracleXYZ (LOCAL=NO)
oracle 13431 1 0 Sep 17 ? 0:00 oracleXYZ (LOCAL=NO)
oracle 21495 1 0 Sep 16 ? 0:00 oracleXYZ (LOCAL=NO)
oracle 13567 1 0 09:17:58 ? 0:00 oracleXYZ (LOCAL=NO)
oracle 4782 1 0 09:42:40 ? 0:00 oracleXYZ (LOCAL=NO)
oracle 25398 1 0 Sep 16 ? 0:00 oracleXYZ (LOCAL=NO)
oracle 21958 1 0 Sep 19 ? 0:01 oracleXYZ (LOCAL=NO)
Thanks in Advance !!!
Krish
We are also experiencing this problem and don't know how to find out what is causing these additional processes to spawn. Anyone?
Deleteconnection leak could be one of the reasons.
DeleteGood article but, where did you get the formula:
ReplyDeleteA basic formula for determining these parameter values is as follows:
processes=x
sessions=x*1.1+5
transactions=sessions*1.1
and as well when we get this error, we'll have to login as
set oracle_sid=YOUR_ORA_SID
sqlplus -prelim sys/oracle as sysdba
-- or
sqlplus /nolog
@> set _prelim on
@> conn sys/oracle as sysdba
processes=x
ReplyDeletesessions=x*1.1+5
transactions=sessions*1.1
NEED PROOF :)
this information is really good and useful. It solved my problem of maximum number of processes (%s) excessed which was giving illusion on dblink as "TNS Listener error, Could not connect".
ReplyDeleteVery ccreative post
ReplyDelete