Processes, sessions and open_cursors

« ORA-28374: typed master key not found in wallet

ORA-12528: TNS:listener: all appropriate instances are blocking new connections »

Processes, sessions and open_cursors

19. 1. 2009, 14.20

Hi,

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

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

So what does mean?

1) This error is related to parameter processes.

SQL> show parameter processes

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

–> It it max value

current value:

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 set 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