ora-00020 maximum number of processes 200 exceeded
I've got a database instance that has apparently run out of processes, however, I cannot even seem to log on as sys to fix things..!!
ora.sujeet # sqlplus / as sysdba
ERROR:
ORA-00020: maximum number of processes (%s) exceeded
Solution:-
If your database is running on a UNIX system, and if you can kill session without disturbing (too much) the application.
You can identify server processes that are connected to the instance, for example, if your instance name is "PRODDB", identify oracle server processes that are connected :
ora.sujeet $ ps -ef | grep oracle PRODDB
kill one & more process (with kill -9 <pid>), After that try to connect you to the instance.
OR
I've got a database instance that has apparently run out of processes, however, I cannot even seem to log on as sys to fix things..!!
ora.sujeet # sqlplus / as sysdba
ERROR:
ORA-00020: maximum number of processes (%s) exceeded
The ORA-00020 is caused by two things:
- Disconnected processes: sonu “sujeet” connections to Oracle that are idle (not working). To fix this, use the ALTER SYSTEM KILL command. You may also need to kill session at the OS level with the KILL -9 command.
- Too few process buckets: Oracle limits the number of connected processes with the processes parameter, and you may get the ORA-00020 error as the natural result in growth of system usage.
Solution:-
If your database is running on a UNIX system, and if you can kill session without disturbing (too much) the application.
You can identify server processes that are connected to the instance, for example, if your instance name is "PRODDB", identify oracle server processes that are connected :
ora.sujeet $ ps -ef | grep oracle PRODDB
kill one & more process (with kill -9 <pid>), After that try to connect you to the instance.
OR
Cause: All process state objects are in use.
Action: Increase the value of the PROCESSES initialization parameter
Simply increase your processes parameter and you are all set!Action: Increase the value of the PROCESSES initialization parameter
or
Checked test db process count it was only 200 as the error says…
Resolution
SQL> Alter system set processes=800 scope=spfile;
System altered.
shutdown immediate;
startup;
startup;
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
No comments:
Post a Comment