ORA-00018: maximum number of sessions exceeded

ORA-00018: maximum number of sessions exceeded

when you get this error always check trace file. In my trace file was this error:

ORA-00020: maximum number of processes (2000) exceeded

So what does this mean?

1) This error is related to parameter processes issues

SQL> show parameter processes

NAME                                 TYPE        VALUE
————————————
processes                            integer     2000

–> It is max value 2000

Check current sessions 

select count(*) from v$process;

COUNT(*)
———-
1122

==> It is ok now. Parameter processes set max value for operating system processes

Parameter Sessions set max. value for session in oracle and should be calculate from parameter processes.

Session=(processes * 1.1 ) +5

In our case (2000 * 1.1) +5 = 2205

SQL> show parameter sessions

NAME                                 TYPE        VALUE
———————————— ———– ——————————
sessions                             integer     2205

–> current number of sessions

select count(*) from v$session;

COUNT(*)
———-
1122

==> we are under limit and this is ok.

Other parameter could be open_cursors. This parameter sets max. number of open cursors per one session.

SQL>show parameter cursors

NAME                                 TYPE        VALUE
———————————— ———– ——————————
open_cursors                         integer     500

–> This mean that each session could have 500 open cursor. Together it is 2205 * 500 = 1102500 cursors.

Now we have open

SQL>select count(*) from v$open_cursor;

COUNT(*)
———-
18319

What value set to parameter open_cursors?

We can calculate 18319 / 2205 = 8.3

–> we can set it to 50 which is default value