ORA-28031: maximum of 148 enabled roles exceeded

IMP-00017: following statement failed with ORACLE error 1918:
 "ALTER USER "ADMIN_WOM" DEFAULT ROLE ALL"
IMP-00003: ORACLE error 1918 encountered
ORA-01918: user 'ADMIN_WOM' does not exist
IMP-00003: ORACLE error 28031 encountered
ORA-28031: maximum of 148 enabled roles exceeded

SQL> select "Grantee", count(*) "Role Number" from
 2 (select distinct connect_by_root grantee "Grantee", granted_role
 3 from dba_role_privs
 4 connect by prior granted_role=grantee)
 5 group by "Grantee"
 6 having count(*)>=148
 7 order by count(*),"Grantee","Role Number"
 8 /

Grantee Role Number
------------------------------ -----------
OOD 153
SYS 168
ADMIN 170

SQL> show parameter max_enable

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

Script na smazání všech. například.
set pagesize 0
select 'revoke ' || granted_role || ' from ADMIN;' from dba_role_privs where granted_role like 'R_AD%';

Komentáře