How to change processes/sessions parameter in Oracle

To change the processes or sessions parameter in Oracle, you need to modify the initialization parameter file (init.ora or spfile.ora) and then restart the Oracle instance.

Here's a step-by-step guide:

1. Identify the Appropriate Parameter:

   • processes: This parameter specifies the maximum number of operating system processes that can connect to Oracle concurrently.
   • sessions: This parameter specifies the maximum number of sessions that can be created in the Oracle instance.

2. Edit the Initialization Parameter File:

   • For traditional initialization parameter files (init.ora), locate and edit the file using a text editor.
   • For server parameter file (SPFILE), use SQL*Plus or Enterprise Manager to modify the parameter.

3. Update the Parameter Value:

• Locate the parameter in the initialization parameter file.
• Change the value of the parameter to the desired value. Ensure that the new value meets your system requirements and is within the supported range.

For example, to change the processes parameter to 300 and the sessions parameter to 350, you might have entries like this in your parameter file:

1. Check the existing values

SELECT NAME,VALUE FROM V$parmeter where name in('processes','sessions');

2. Change the values:

-- if the database is started through spfile.

ALTER SYSTEM SET PROCESSES=7000 SCOPE=SPFILE;

-- if it is started using init<SID>.ora file

then edit the file and change the values

3. Restart (Bounce) the Oracle Instance:

• Restart the Oracle instance to apply the changes. This can typically be done using SQL*Plus or Enterprise Manager, or by using operating system commands to stop and start the Oracle services.

For example, using SQL*Plus to restart the instance:

SHUTDOWN IMMEDIATE;

STARTUP;

4. Verify the Changes:

• After restarting the instance, verify that the changes have been applied by querying the V$PARAMETER view:

SELECT NAME,VALUE FROM V$parmeter where name in('processes','sessions');

This query will show you the current values of the processes and sessions parameters.

By following these steps, you can successfully change the processes or sessions parameter in Oracle.

Always ensure to take appropriate precautions and test changes in a non-production environment before applying them to a production system.