alter 日志出现大量的Maximum of 148 enabled roles exceeded for user ZLHIS. Not...

jiawang

现象如下：Fri Jun 14 09:52:50 2019
Maximum of 148 enabled roles exceeded for user ZLHIS. Not loading all the roles.
Fri Jun 14 09:52:50 2019
Maximum of 148 enabled roles exceeded for user ZLHIS. Not loading all the roles.
Fri Jun 14 09:52:51 2019
Maximum of 148 enabled roles exceeded for user ZLHIS. Not loading all the roles.


报错原因：用户zlhis最多超过了148个启用的角色

查看当前用户zlhis用户角色数
SQL> select "Grantee", count(*) "Role Number" from
2 (
3 select distinct connect_by_root grantee "Grantee", granted_role
4 from dba_role_privs
5 connect by prior granted_role=grantee
6 ) where GRANTEE='ZLHIS'
7 group by "Grantee"
8 order by "Grantee","Role Number"
9 /

GRANTEE COUNT(*)
—————————— ———-
ZLHIS 149



SQL> show parameter max_enabled_roles

NAME TYPE VALUE
———————————— ———-- ————
max_enabled_roles integer 150



由此可见，当前用户zlhis已启用149个角色超过系统最大值148个角色，解决办法删除一部分角色。

具体可以参照What to Check When Dealing With Ora-28031: Maximum Of 148 Enabled Roles Exceeded? (Doc ID 778785.1)

郑全

通过这个下面这个语句可以解决：
alter user zlhis default role dba