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


Everything works ok, right?

Now, a little later, lets try some DDL:


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:


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:


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:



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):


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


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:


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?

1. Oracle uses recursive sessions for recursive data dictionary operations
2. These sessions are also taken from session state object array controlled by sessions parameter
3. V$SESSION does not show recursive sessions, but V$RESOURCE_LIMIT tells you the truth about session state object array utilization
4. If you hit the ORA-00018 error, then make your sessions parameter array larger or configure your application to use less connections or sessions
   

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.