Recursive Sessions and ORA-00018: maximum number of sessions exceeded

Check this out, I can log on, run queries, update rows:

SQL> select count(*) from t;

  COUNT(*)

----------

     50079

SQL> select count(*) from t;

  COUNT(*)

----------

     50079

SQL> delete t where rownum = 1;

1 row deleted.

SQL> commit;

Commit complete.

Everything works ok, right?

Now, a little later, lets try some DDL:

SQL> alter table t move;

alter table t move

*

ERROR at line 1:

ORA-00018: maximum number of sessions exceeded

SQL> drop table t;

drop table t

*

ERROR at line 1:

ORA-00018: maximum number of sessions exceeded

What the heck? I am logged on already! How come I get the "maximum number of sessions" exceeded error message?

This error message says we have tried to create a new session but have ran out of session state objects in Oracle... hmm... why does Oracle create a NEW session as I am already logged on !!!

This is because a DDL statement requires use of recursive DML and some recursive (data dictionary) operations in Oracle are done using a recursive session context! This is, that Oracle will silently allocate a new session state object from session state object array (V$SESSION / X$KSUSE) and all the recursive operations state objects (locks, transaction state objects etc) will belong to that recursive session. This separation simplifies privilege management when accessing data dictionary base tables and also helps with cleanout should the data dictionary operation fail.

In fact, also data dictionary QUERIES (populating dictionary cache) are done using a separate recursive session, check this:

SQL> select count(*) from t;

  COUNT(*)

----------

     50078

SQL> alter system flush shared_pool;

System altered.

SQL> select count(*) from t;

select count(*) from t

                     *

ERROR at line 1:

ORA-00018: maximum number of sessions exceeded

The last query failed now as the flush shared_pool operation cleared also dictionary cache and during next parse a data dictionary operation was required.

So, it's pretty evident that we have ran out of sessions (in other words, session state objects or slots in V$SESSION session state object array).

Let's check what's the sessions parameter value and how many sessions V$SESSION reports as used:

SQL> select value from v$parameter where name = 'sessions';

VALUE

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

170

SQL>

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

  COUNT(*)

----------

       163

The sessions parameter is 170 and only 163 sessions are reported to be used by V$SESSION! We should have 7 session still left?!

Doesn't make sense... let's double check from V$RESOURCE_LIMIT which shows us the usage of various fixed and segmented arrays in Oracle instance, including the session state object array, V$SESSION:

SQL> select * from v$resource_limit;

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU

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

processes                                      129             136        150        150

sessions                                       170             170        170        170

enqueue_locks                                   13              23       2300       2300

enqueue_resources                               13              40        968  UNLIMITED

ges_procs                                        0               0          0          0

ges_ress                                         0               0          0  UNLIMITED

ges_locks                                        0               0          0  UNLIMITED

ges_cache_ress                                   0               0          0  UNLIMITED

ges_reg_msgs                                     0               0          0  UNLIMITED

ges_big_msgs                                     0               0          0  UNLIMITED

ges_rsv_msgs                                     0               0          0          0

gcs_resources                                    0               0          0          0

gcs_shadows                                      0               0          0          0

dml_locks                                        0              47        748  UNLIMITED

temporary_table_locks                            0               3  UNLIMITED  UNLIMITED

transactions                                     0              12        187  UNLIMITED

branches                                         0               0        187  UNLIMITED

cmtcallbk                                        0               2        187  UNLIMITED

sort_segment_locks                               0               3  UNLIMITED  UNLIMITED

max_rollback_segments                           11              11        187      65535

max_shared_servers                               1               1  UNLIMITED  UNLIMITED

parallel_max_servers                             0               2         40       3600

The above output shows that the "sessions" array is completely full, all 170 slots of max 170 have been used!

So, V$SESSION is wrong! It does not report all sessions really in use. If you look into V$SESSION view text (with help of V$FIXED_VIEW_DEFINITION), you'll see that V$SESSION reports only USER and BACKGROUND sessions.

But there's a 3rd type of a session - a RECURSIVE session, which is used for recursive data dictionary calls as explained above. V$SESSION doesn't show these.

Here's a little query snippet which queries X$KSUSE and also reports recursive sessions. To test this identified the PADDR of one of my sessions (in a different database, where I wasn't out of session state objects), caused some serious performance problems and then tried to drop a table (which should allocated a recursive session for the duration of data dictionary DML):

SQL> select paddr from v$session where sid = userenv('sid');

PADDR

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

0000000398E73B78

SQL>

SQL> create table t3(a int);

...session got hung here (or rather became very slow...)

In another session I ran this query, to see which sessions belong under that process state object identified above:

SQL> select decode(bitand(ksuseflg,19),17,'BACKGROUND',1,'USER',2,'RECURSIVE','?'),ksuudsna

  2  from x$ksuse s where ksusepro = '0000000398E73B78';

DECODE(BITAND(KSUSEFLG,19),17,   KSUUDSNA

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

USER                             SYSTEM

RECURSIVE                        SYS

You see, X$KSUSE says there are two sessions under the process, one regular user session (SYSTEM user) and another recursive session which run under SYS security context.

By the way, the recursive session state objects are not linked directly under the process state object (like normal sessions are, but instead they are under a call state object under your regular session, as seen from the following process state dump:

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

    SO: 38f4ac000, type: 3, owner: 398e73b78, flag: INIT/-/-/0x00

    (call) sess: cur 398f149b8, rec 398f6b3c8, usr 398f149b8; depth: 0

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

      SO: 398f6b3c8, type: 4, owner: 38f4ac000, flag: INIT/-/-/0x00

      (session) sid: 150 trans: 0, creator: 0, flag: (2) -/REC -/-/-/-/-/-

                DID: 0000-0000-00000000, short-term DID: 0000-0000-00000000

                txn branch: 0

                oct: 0, prv: 0, sql: 0, psql: 0, user: 0/SYS

      temporary object counter: 0

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

    SO: 38f461ae0, type: 16, owner: 398e73b78, flag: INIT/-/-/0x00

    (osp req holder)

Note the REC bit stating that this is a recursive session (as opposed to USR/SYS for user and background sessions).

So, what's the moral of this story?

Note that in Oracle 11.2 the automatic calculation of sessions parameter has changed and many more session state objects are allocated for a given number of processes by default.