Note Id "What to Check When Dealing With Ora-28031: Maximum Of 148 Enabled Roles Exceeded? (Doc ID 778785.1)" explains this well
#1. One must check first the number of roles granted to a user or role. It is useful to know that, with the sole exception of IDENTIFIED GLOBALLY roles, as documented, the roles created by a user are automatically granted to that user.Check the number of roles that have been granted to all users and roles, using the following query:
This query should indicate the number of roles that have been granted to all users, including roles that have been granted via roles.
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"
order by "Grantee","Role Number"
/
select distinct connect_by_root grantee,granted_role
from dba_role_privs
connect by prior granted_role=grantee
start with grantee='';
Keeping in mind, as seen at:
MAX_ENABLED_ROLES
documentation page, that the number of enabled roles for a user cannot be larger than 148, if any of the above users has more than 148 roles enabled, some policy should be envisaged so that the roles are not be enabled all at once.
Of course, the easiest thing that can be done is to reset the number of default roles for the user, as indicated at the ALTER USER documentation page:
alter user default roles
- ;
The list of roles can be: an explicit list of roles, all roles or all roles except
- .
Sometimes, it happens that sessions running as SYS (this is mostly common in job sessions) fail with the ORA-28031 and the user SYS has indeed more than 148 roles. However, normal sessions don't have any problems connecting and running commands in the database.
The SYS user does not require all the roles that have been defined in the database. Simply set DBA as the only default role for sys:
alter user SYS default roles DBA;
and this should eliminate the error when running statistics taking jobs or advisories.
The most difficult scenario to address situation is when the application enables or disables roles and, for some reasons, the number of jobs defined for a user surpasses 148. In this situation, the application must be checked and commands like:
set roles all;
or
execute dbms_session.set_role('ALL');
or
execute dbms_session.set_role('ALL');
should be replaced with commands enabling only specific roles, which are, of course, less than 148.
