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 11.2.0.4. 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>
SQL> conn scott/tiger
Connected.
SQL> select count(*) from session_roles;
COUNT(*)
----------
148
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.
Learning is not a spectator sport 
Is this a SQL-only nice touch? I get an exception when I try to gather statistics:
> EXEC DBMS_STATS.GATHER_TABLE_STATS( ‘SCOTT’, ‘EMP’ ) ;
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
That’s interesting. I tried a few other built-ins (DBMS_UTILITY, DBMS_SQL, …) and they were fine. I wonder if its been explicitly added to DBMS_STATS for some reason.
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,
Iudith
looks like the parameter got removed in 12.2
rajesh@ORA11G> show parameter max_enable
NAME TYPE VALUE
———————————— ———– ————–
max_enabled_roles integer 150
rajesh@ORA11G> conn rajesh/oracle@ora12c
Connected.
rajesh@ORA12C> show parameter max_enable
rajesh@ORA12C>