ORA-00020: No more process state objects available

 How to increase processes, sessions, and transactions parameters in Oracle 12R1.

Whenever DB processes are utilized 100% then it’s NOT allowed any new sessions/connections to DB’s instances and we get the below error:

ORA-00020: No more process state objects available

ORA-20 errors will not be written to the alert log for

If we are planning to increase “PROCESSES” parameter so, we  should also plan to increase the “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

Cause: All process state objects are in use.

Action: Increase the value of the PROCESSES initialization parameter.

Before starting on the solution, we understand what is processes, sessions & transactions:

PROCESSES: It specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, 

job queue processes, and parallel execution processes.

The default values of the SESSIONS and TRANSACTIONS parameters are derived from the PROCESSES parameter. Therefore, if we change the value of PROCESSES, 

We should evaluate whether to adjust the values of those derived parameters.

SESSIONS: It specifies the maximum number of sessions that can be created in the system. Because every login requires a session, this parameter effectively determines the maximum number of concurrent users in the system. 

We should always set this parameter explicitly to a value equivalent to our estimate of the maximum number of concurrent users, plus the number of background processes, plus approximately 10% for recursive sessions. Oracle uses the default value of this parameter as its minimum. Values between 1 and the default do not trigger errors, but Oracle ignores them and uses the default instead.

TRANSACTIONS: It specifies the maximum number of concurrent transactions. Greater values increase the size of the SGA and can increase the number of rollback segments allocated. 

The default value is greater than SESSIONS (and, in turn, PROCESSES) to allow for recursive transactions.

Solution:-

[oradb@oracle ~]$ sqlplus / as sysdba

SQL> show parameter processes

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

aq_tm_processes                      integer     1

db_writer_processes                  integer     1

gcs_server_processes                 integer     0

global_txn_processes                 integer     1

job_queue_processes                  integer     2

log_archive_max_processes            integer     4

processes                            integer     400

SQL> show parameter sessions

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

java_max_sessionspace_size           integer     0

java_soft_sessionspace_limit         integer     0

license_max_sessions                 integer     0

license_sessions_warning             integer     0

sessions                             integer     800

shared_server_sessions               integer

SQL> show parameter transactions

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

transactions                         integer     880

transactions_per_rollback_segment    integer     5

SQL>

If we are planning to increase “PROCESSES” parameter so, we  should also plan to increase the “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

Before altering parameters we need to take a backup of pfile.

SQL> create pfile from spfile;

SQL> alter system set processes=500 scope=spfile;

SQL> alter system set sessions=555 scope=spfile;

SQL> alter system set transactions=610 scope=spfile;

After resetting the parameters we need to bounce the database-

SQL>shutdown immediate;

SQL>startup;

After that will check the settings of parameters-

SQL> show parameter processes;

SQL> show parameter session;

SQL> show parameter transactions;