License_max_sessions

(Tested on an Oracle 10 database.)

If license_max_sessions is set to zero (the default), there is no limit to the number of concurrent user sessions:

SQL> select value

  2  from v$parameter

  3  where name = 'license_max_sessions'

  4  /

VALUE

----------

0

SQL>

But the documentation does not make it clear if this figure includes:

• All sessions.
• Only sessions where the username is not null (i.e. excluding Oracle's background processes).
• Sessions for users with the restricted session privilege.

This example tries to clarify this. First create a test user:

SQL> grant create session to andrew identified by reid

  2  /

Grant succeeded.

SQL>

Then set the parameter to a suitable value for testing:

SQL> alter system set license_max_sessions = 6;

System altered.

SQL>

And set up a series of simultaneous sessions:

SQL> conn andrew/reid

Connected.

SQL>

Until you see the following error:

SQL> conn andrew/reid
ERROR:
ORA-00019: maximum number of session licenses exceeded

Warning: You are no longer connected to ORACLE.
SQL>

At this point the following message appears in the alert log:

Non-DBA logon denied; current logons equal maximum (6)

Then count the sessions in the database:

SQL> l
  1  select nvl(username,'NULL'), count(*)
  2  from v$session
  3* group by nvl(username,'NULL')
SQL> /

NVL(USERNAME,'NULL')             COUNT(*)
------------------------------ ----------
ANDREW                                  3
NULL                                   15
SYS                                     2

SQL>

So it looks to me as if:

• The background processes are not counted.
• Sessions for users (e.g. SYS) with the restricted session privilege are counted.
• The actual number of sessions allowed is 1 less than the limit set by license_max_sessions. I am a bit suspicious of this so I hope to recheck it on some other systems and report back.

If you grant restricted session to andrew, this allows him to log in again:

SQL> conn andrew/reid
ERROR:
ORA-00019: maximum number of session licenses
exceeded

SQL> conn / as sysdba

Connected.
SQL> grant restricted session to andrew;

Grant succeeded.

SQL> conn andrew/reid
Connected.
SQL>

And a further message appears in the alert log:

License maximum (6) exceeded, DBA logon allowed

There appears to be nothing to stop you setting license_max_sessions to a number lower than the current number of sessions. Note that we now have 6 sessions as Andrew was able to connect again once he had the restricted session privilege:

SQL> select count(*) from v$session
  2  where username is not null;

  COUNT(*)
----------
        6

SQL> alter system set license_max_sessions = 4;

System altered.

SQL>