Oracle Recursive Sessions ORA-00018

Today I want to talk about Oracle Database session usage; last week there was a problem with a database reporting ORA-00018: maximum number of sessions exceeded, but the number of sessions from v$session was much less than the one specified in SESSIONS parameter.

So, what was going on?

Turns out this is explained by internal recursive sessions that also account but they are not seen in the dictionary views. Note ID 419130.1 explains this in detail but I wanted to see this behavior in my 12c test database.

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

  COUNT(*)

----------

       104

SQL> select count(*) from x$ksuse where bitand(ksspaflg,1) !=0 ;

  COUNT(*)

----------

       132

Also wanted to know what sessions were recursive and seems like all of them are performing DDL operations.

SQL> select INDX,decode(bitand(ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?'),ksuudsna from x$ksuse s WHERE decode(bitand(ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?')='RECURSIVE';

      INDX DECODE(BIT KSUUDSNA

---------- ---------- ------------------------------

         8 RECURSIVE  SYS

        11 RECURSIVE  SYS

        14 RECURSIVE  SYS

        15 RECURSIVE  SYS

        17 RECURSIVE  SYS

        19 RECURSIVE  SYS

        20 RECURSIVE  SYS

        21 RECURSIVE  SYS

        29 RECURSIVE  SYS

        32 RECURSIVE  SYS

        38 RECURSIVE  SYS

        39 RECURSIVE  SYS

        40 RECURSIVE  SYS

        42 RECURSIVE  SYS

       123 RECURSIVE  SYS

       126 RECURSIVE  SYS

       129 RECURSIVE  SYS

       134 RECURSIVE  SYS

       136 RECURSIVE  SYS

       137 RECURSIVE  SYS

       138 RECURSIVE  SYS

       139 RECURSIVE  SYS

       142 RECURSIVE  SYS

       144 RECURSIVE  SYS

       145 RECURSIVE  SYS

       148 RECURSIVE  SYS

       154 RECURSIVE  SYS

       155 RECURSIVE  SYS

       156 RECURSIVE  SYS

       157 RECURSIVE  SYS

       242 RECURSIVE  SYS

       247 RECURSIVE  SYS

       249 RECURSIVE  SYS

       250 RECURSIVE  SYS

       252 RECURSIVE  SYS

       253 RECURSIVE  SYS

       255 RECURSIVE  SYS

       257 RECURSIVE  SYS

       259 RECURSIVE  SYS

       264 RECURSIVE  SYS

       265 RECURSIVE  SYS

       269 RECURSIVE  SYS

       272 RECURSIVE  SYS

       273 RECURSIVE  SYS

       275 RECURSIVE  SYS

       276 RECURSIVE  SYS

       278 RECURSIVE  SYS

       367 RECURSIVE  SYS

       369 RECURSIVE  SYS

       371 RECURSIVE  SYS

       375 RECURSIVE  SYS

       376 RECURSIVE  SYS

       379 RECURSIVE  SYS

       381 RECURSIVE  SYS

       383 RECURSIVE  SYS

       384 RECURSIVE  SYS

       388 RECURSIVE  SYS

       389 RECURSIVE  SYS

       391 RECURSIVE  SYS

       392 RECURSIVE  SYS

60 rows selected.

This is something to keep in mind when setting SESSIONS parameter in future.

Thanks,

Alfredo