Skip to content
Home » Oracle » Oracle Parameter SESSIONS, How and Why

Oracle Parameter SESSIONS, How and Why

In this post, we'd like to talk about the following topics for you to manage SESSIONS parameter better.

  1. Default Value of SESSIONS
  2. How to Check SESSIONS ?
  3. How to Change SESSIONS ?

Default Value of SESSIONS

SESSIONS is an initialization parameter of Oracle database, which limits the maximum number of sessions that can be reached in the instance.

In fact, the default value of SESSIONS is derived from PROCESSES, so we rarely change it.

Derivation of SESSIONS for Every Releases
ReleaseSESSIONS Calculation
11.1SESSIONS = PROCESSES * 1.1 + 5
11.2SESSIONS = PROCESSES * 1.5 + 22
12cSESSIONS = PROCESSES * 1.5 + 22
18cSESSIONS = PROCESSES * 1.5 + 22
19cSESSIONS = PROCESSES * 1.5 + 22
21cSESSIONS = PROCESSES * 1.5 + 22

Basically, the value of SESSIONS is to multiply PROCESSES by a factor and then add a constant. So the final number might look a little awkward, e.g. 2272 which we can see the example soon.

As you can see in the above list, there's a division from release 11.2, it starts to use a higher factor to make the value larger than before.

How to Check SESSIONS ?

First of all, we look up parameter PROCESSES and SESSIONS like this:

SQL> column name format a30;
SQL> column value format a30;
SQL> select name, value from v$parameter where name in ('processes', 'sessions') order by 1;

NAME                           VALUE
------------------------------ ------------------------------
processes                      1500
sessions                       2272

The value 2272 for SESSIONS is actually derived from PROCESSES. That is, 1500 * 1.5 + 22 = 2272. Someone doesn't know the story might raise questions about it.

How to Change SESSIONS ?

Although the default value of SESSIONS is derived from PROCESSES, it can be changed into a more meaningful value, say, 3000.

Most likely, you will need to raise SESSIONS only when your database applies Shared Server Architecture.

Please note that, any value less than the default one will be ignored at startup without any warning, the instance still take and use the default one. In other words, the derived value is the minimum one.


First of all, we backup SPFILE in case that something goes wrong.


Such parameter can only be changed within SCOPE=SPFILE.

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

System altered.

The default behavior is to change all instances of the database, so you don't have to worry about the command if you're in a RAC database.

Restart Database

To make new value take effect, we have to restart the database to apply SPFILE from startup.

Check Result

Let's see the result.

SQL> select name, value from v$parameter where name in ('processes', 'sessions') order by 1;

NAME                           VALUE
------------------------------ ------------------------------
processes                      1500
sessions                       3000

Finally, we make SESSIONS become 2 times of PROCESSES.

At the state of computer technology, we usually deploy application servers to control incoming sessions in a connection pool, so tuning the value of SESSIONS is not as regular as the old days.

Leave a Reply

Your email address will not be published. Required fields are marked *