ORA-28031: maximum enabled roles exceeded

So this is what I got…..

 

ORA-28031: maximum of 150 enabled roles exceeded

ORA-06512: at "SYS.UTL_RECOMP", line 865

 

I checked the parameter….

 

Show parameter max_enabled_roles

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

max_enabled_roles                    integer     150

 

But if you try to increase this, it won’t work.

 

SQL> alter system set max_enabled_roles = 200 scope = spfile;

alter system set max_enabled_roles = 200 scope = spfile

*

ERROR at line 1:

ORA-00068: invalid value 200 for parameter max_enabled_roles, must be between 1 and 148

 

Which is curious because its already set to 150 regardless of what the message says.

 

So I had to find out which users were to blame

 

select "Grantee", count(*) "Role Number" from
(select distinct connect_by_root grantee "Grantee", granted_role
from dba_role_privs
connect by prior granted_role=grantee)
group by "Grantee"
having count(*)>=148
order by count(*),"Grantee","Role Number"
/

and then revoke some roles to bring the number down.