在alert_.log里报如下错误:

ORA-12012: error on auto execute of job 2
ORA-28031: maximum of 148 enabled roles exceeded

今天一同事提到ORA-28031错误，错误信息明显的提示了授予用户role的数量超过148个导致的。这些role的数量，包括直接授予的，也包括间接授予的。当一个用户被直接和间接授予的role超过148个时，导致的直接问题是用户不能登录数据库，布置的后台job也不能执行。有一个例外就是sys可以拥有超过148个role,它可以正常的登录数据库，但后台sys的JOB不能正常的跑。同时要注意，创建role的用户，隐含的被授予了这些role.metalink 780749.1有关错ORA-28031的详细内容。个人猜测，当一个普通session初始化时，要分配一定的内存,当分配的role所占内存超过了某一限制，就不能创建session了.

下面是测试普通用户，
环境：OS: Linux version 2.6.9-67.ELsmp, DB:10.2.0.4

SYS@ ORCL>create user w1 identified by w1;

User created.

SYS@ ORCL>create user w2 identified by w2;

User created.

SYS@ ORCL>grant create session,create role to w1;

Grant succeeded.

SYS@ ORCL>grant create session to w2;

Grant succeeded.

SYS@ ORCL>conn w1/w1
Connected.

W1@ ORCL>create role role_00;

Role created.

W1@ ORCL>begin
2 for i in 1..147 loop
3 execute immediate 'create role role'||i;
4 execute immediate 'grant role'||i||' to role_00';
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

W1@ ORCL>grant role_00 to w2;

Grant succeeded.

W1@ ORCL>conn w2/w2
Connected.

从这里可以看出，用户w2有148个role:role_00+role[1..147], 可以正常的连接数据库。

W2@ ORCL>conn w1/w1
Connected.

W1@ ORCL>create role role148;

Role created.

W1@ ORCL>grant role148 to role_00;

Grant succeeded.

W1@ ORCL>conn w2/w2
ERROR:
ORA-28031: maximum of 148 enabled roles exceeded

当w2超过148个role时，不能登录数据库了。

Warning: You are no longer connected to ORACLE.
@ >conn w1/w1
ERROR:
ORA-28031: maximum of 148 enabled roles exceeded

所有这些role是w1创建的，w1被默认的的授予了这些权限.此时W1已经创建了149个role了，也不能登录数据库了.

@ >conn / as sysdba
Connected.

SYS@ ORCL>select count(1) from dba_role_privs where grantee='W1';

COUNT(1)
----------
149

SYS@ ORCL>select count(1) from dba_role_privs where default_role='YES' start with grantee='W2' connect by prior granted_role=grantee;

COUNT(1)
----------
149

对用户Sys的测试来看，当role数量超过148时，能够正常的登录数据库，布置的后台job会报ORA-28031,但貌似不是每次执行job都报错误。