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;
