Wednesday, April 13, 2011

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>

No comments: