if you face an error like this:
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
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;