How Oracle role enables number limits

Oracle allows up to 148 enabled when user roles are enabled.

In fact, this limit is strange, because 148 and 2 of the power difference is large, do not know what the reason for Oracle to determine this value:

Sql> CREATE USER U_role identified by U_password;

User has created.

sql> GRANT CREATE role to U_role;

The authorization was successful.

Sql> GRANT CREATE session to U_role;

The authorization was successful.

Sql> CONN U_role/u_password

is connected.

Sql> BEGIN

2 for I in 1..148 LOOP

3 EXECUTE IMMEDIATE ' CREATE role r_ ' | | I;

4 End LOOP;

5 end;

6/

The PL/SQL process has completed successfully.

Sql> SELECT * from Session_roles;

No rows selected

Sql> CONN U_role/u_password

is connected.

Sql> SELECT COUNT (*) from Session_roles;

COUNT (*)

----------

148

sql> CREATE role r_149;

The role has been created.

Sql>connu_role/u_password

ERROR:

Ora-28031:maximum of 148 enabled roles exceeded

Warning: You are no longer connected to Oracle.

This column more highlights: http://www.bianceng.cn/database/Oracle/

Because the role is enabled when the session is logged on, the record is not queried from the session_roles after 148 roles have been created. The 149th character was created, and Oracle made an error when landing again.

Sql> Conn/as SYSDBA

is connected.

sql> DROP role r_149;

The role has been deleted.

Sql> CONN U_role/u_password

is connected.

sql> CREATE role r_149;

The role has been created.

sql> SET role All;

SET role All

*

Line 1th Error:

ORA-28031: exceeds the maximum number of enabled roles 148

Note that the total number of authorized roles limits includes the number of roles that are directly authorized and those that are authorized to the role:

sql> DROP role r_149;

The role has been deleted.

sql> DROP role r_148;

The role has been deleted.

Sql> Conn/as SYSDBA

is connected.

sql> CREATE role r_148;

The role has been created.

Sql> GRANT r_148 to U_role;

The authorization was successful.

Sql> CONN U_role/u_password

is connected.

Sql> Conn/as SYSDBA

is connected.

sql> CREATE role r_149;

The role has been created.

Sql> GRANT r_149 to r_148;

The authorization was successful.

Sql>connu_role/u_password

ERROR:

Ora-28031:maximum of 148 enabled roles exceeded

Warning: You are no longer connected to Oracle.