How to increase number of process in oracle : 3 EASY STeps

In our article, we have already discussed the ORA-00020: maximum number of processes exceeded error. So, today we will discuss its workaround to increase number of process in Oracle database. Let’s go through the steps :

Notes to Remember :

• PROCESSES affect SESSIONS and TRANSACTIONS parameters. If we change the number of processes, the session and transaction parameters will also change automatically.
• Changing this value requires a restart of the database.
  

Steps to Increase Number of PROCESS in Oracle:

Step 1: Check the Number of Process

show parameter process

Step 2: For reference, check the session parameter value

show parameter sessions;

Use this formula :

SESSIONS = (1.1 * PROCESSES) + 5

So, if you increase PROCESSES to 1000, then the session will be

SESSIONS = (1.1 * 1000) + 5=1105

Step 3: For reference, check the transaction parameter value

show parameter transactions;

Use this formula :

TRANSACTIONS = (1.1 * SESSIONS)

Step 4: Take a backup of the spfile

create pfile=<ORACLE_HOME>/dbs/init_<dbsid>.ora from spfile;

Step 5: Steps to change the Number of Process

ALTER SYSTEM SET processes=5=1000 SCOPE=SPFILE;

Step 6: Validation of the Number of Processes

show parameter process;

So now you got the workaround for ORA-00020. But before making this change in the production environment, check with your manager and be cautious as well.

So, here are the simple steps that you can understand to increase the number of sessions in Oracle. Hope this helps!! Also, you can get more information about the process parameter from the Oracle Documentation as well.