max_enabled_roles – nice touch

Posted by

Some people get very excited with roles, and quickly the number of roles proliferates to huge numbers…Until this happens

ORA-28031: maximum of 148 enabled roles exceeded

But in helping someone out on AskTom, I just found a nice touch in  I had granted my account 200 roles (since the max_enabled_roles parameter is capped at 148), fully expecting to get the standard error when attempting to connect.  But I didn’t – all was fine.  You can see what is going on via the following demo.

SQL> begin
  2  for i in 1 .. 200 loop
  3    execute immediate 'create role rr'||i;
  4    execute immediate 'grant rr'||i||' to scott';
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> conn scott/tiger

SQL> select count(*) from session_roles;


So even though I granted 200 roles, when I queried SESSION_ROLES, I did not see all 200 , I only saw 148.

My alert.log shows this

Wed Nov 30 10:32:35 2016
Maximum of 148 enabled roles exceeded for user SCOTT. Not loading all the roles.

Some people might prefer an explicit error, but hopefully any good alert log monitoring would quickly pick this up.  And since if you exceed 148 there is no workaround (except to reduce the number of roles granted), I think avoiding the error is a better strategy.


  1. Is this a SQL-only nice touch? I get an exception when I try to gather statistics:


    ORA-28031: maximum of 150 enabled roles exceeded
    ORA-06512: at “SYS.DBMS_STATS”, line 24281
    ORA-06512: at “SYS.DBMS_STATS”, line 24332
    ORA-06512: at line 1

  2. I still remember that in older database versions (I think it was 8i ) we got an error when trying to connect,
    if more than “n” roles were granted (as default roles).

    To tell the truth, I don’t see any logical reason to limit the number of enabled roles …
    but, if such as limit does exist, then it is better to raise an error already when attempting to enable those roles (like on connect, if all the roles are default roles) than to arbitrarily enable 148 of them,
    which might require not easy debugging for other errors that might be subsequently raised.

    The parameter max_enabled_roles is documented as deprecated, but the hard limit of 148 user roles
    is documented under the SET ROLE command … so it looks like the trend is to maintain this limit,
    rather than remove it …

    Best Regards,

  3. looks like the parameter got removed in 12.2

    rajesh@ORA11G> show parameter max_enable

    ———————————— ———– ————–
    max_enabled_roles integer 150
    rajesh@ORA11G> conn rajesh/oracle@ora12c
    rajesh@ORA12C> show parameter max_enable

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.