Apr 13 2016

ORA-00020: maximum number of processes (string) exceeded

Category: Errors and SolutionsFatih Acar @ 11:09

Error: ORA-00020: maximum number of processes (string) exceeded

Cause: All process state objects are in use.

Action: Increase the value of the PROCESSES initialization parameter.

Solution: You have to increase processes parameter value.

ORA-00020 is caused by two things:

1- A lot of idle that is not working (zombie) connection. You can kill this connections from database. Maybe also you have to kill proceses at the operating system level witk kill -9 command.

2- Too few process parameter value. Oracle provides limit for the number of connected processes with the processes parameter. Also you may get the ORA-00020 error due to system resourse limit.

Calculating amount of sessions and transactions parameter values with below formula.

processes=x
sessions=x*1.1+5
transactions=sessions*1.1

You can show and increase processes parameter like below.

SQL> show parameter processes;
processes integer 150
SQL> alter system set processes=300 scope=spfile;
SQL> shutdown immediate;
SQL> startup;

You should set the processes parameter to a very high value because it consumers very little resources and you do not want your instance to “lock up” with the ORA-0018 error.

Also, you can look this topic: https://www.fatihacar.com/blog/ora-00018-maximum-number-of-sessions-exceeded/

2,711 total views, 4 views today

Tags: ORA-00018, ORA-00020, Oracle Error Solutions


Apr 12 2016

ORA-00018 maximum number of sessions exceeded

Category: Errors and SolutionsFatih Acar @ 10:37

Error: ORA-00018 maximum number of sessions exceeded

Cause: All session state objects are in use.

Action: Increase the value of the SESSIONS initialization parameter.

Solution: You have to increase processes and depends on this parameter which is sessions, transactions parameters.

Calculating amount of sessions and transactions parameter values with below formula.

processes=x
sessions=x*1.1+5
transactions=sessions*1.1

You can show and increase processes parameter like below.

SQL> show parameter processes;
processes integer 150
SQL> alter system set processes=300 scope=spfile;
SQL> shutdown immediate;
SQL> startup;

You should set the processes parameter to a very high value because it consumers very little resources and you do not want your instance to “lock up” with the ORA-0018 error.

11,521 total views, no views today

Tags: ORA-00018, ORA-00020, Oracle Error Solutions