ora-28031 maximum of 148 enabled roles exceeded

if you face an error like this:

Image

 

Description of the Problem: 

When a new session is initiated Oracle will attempt to enable all the default roles of the account if their number will be less than ( (value of the MAX_ENABLED_ROLES parameter) – 2 ). The default value of MAX_ENABLED_ROLES is 150. If the account is granted (directly or recursively) more than 148 roles then the session creation will fail with ORA-28031.

SQL> show parameter max_enabled_roles 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

max_enabled_roles                    integer     150

Image

To Resolve this error:

You need to remove the role from the user as a “default” one.

So, the only way is to revoke then re-grant the role again without the “default option”. 

REVOKE RL_SELECT_EMP FROM EMPLOYEE;

GRANT RL_ SELECT_EMP TO EMPLOYEE;