重庆思庄技术分享——alter 日志出现大量的Maximum of 148 enabled roles exceeded f

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

现象如下：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)