Tom,

Thanks for maintaining such a good website!!!

Once in awhile we get the following Oracle message when attempting to login to Oracle.

ORA-28031: maximum of 148 enabled roles exceeded
(was surprised no one asked you about this yet)

This error prevents the user from logging into the db environment. (SYS still has access)

As a result I look through the list of roles we have and I drop the ones that are not being used anymore. Is there a better way of managing the roles?

Wouldn't this query give me a list of unassigned roles?

select drp.granted_role, drp.grantee, dr.role, dr.password_required
from dba_roles dr left outer join dba_role_privs drp
on dr.role = drp.granted_role
where granted_role is null
order by role;

This DB environment is a development environment so there are lots of schemas/roles. And schemas/roles are dropped and created on a daily basis.

Thanks,
Victor