[20151110]oracle可以启用多少role.txt

--链接http://blog.itpub.net/4227/viewspace-676078/

Oracle在启用用户的角色时，最多允许启用148个。

其实这个限制比较奇怪，因为148与2的幂相差较大，不知道Oracle出于什么原因确定的这个值：

BEGIN

FOR I IN 1..148 LOOP EXECUTE IMMEDIATE 'CREATE ROLE R_' || I; END LOOP; END; /

CREATE ROLE R_149;

SCOTT@test01p> SET ROLE ALL;

SET ROLE ALL * ERROR at line 1: ORA-28031: maximum of 148 enabled roles exceeded

SCOTT@test01p> SET ROLE ALL;

SET ROLE ALL * ERROR at line 1: ORA-28031: maximum of 148 enabled roles exceeded

--我自己查询参数参数:

SYS@book> @ &r/ver1

PORT_STRING                    VERSION        BANNER

------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> show parameter role

NAME               TYPE     VALUE ------------------ -------- ------ max_enabled_roles  integer  150 os_roles           boolean  FALSE remote_os_roles    boolean  FALSE

SYS@book> alter system set max_enabled_roles=151 scope=spfile;

alter system set max_enabled_roles=151 scope=spfile * ERROR at line 1: ORA-00068: invalid value 151 for parameter max_enabled_roles, must be between 1 and 148

--无法更改这个数值更大. 不过还是觉得很奇怪150 与 148 相差2. 难道缺省有2个是默认的.

--最容易想到的public,参看dba_roles的视图定义:

CREATE OR REPLACE FORCE VIEW SYS.DBA_ROLES

(    ROLE   ,PASSWORD_REQUIRED   ,AUTHENTICATION_TYPE ) AS    SELECT name          ,DECODE           (              password             ,NULL, 'NO'             ,'EXTERNAL', 'EXTERNAL'             ,'GLOBAL', 'GLOBAL'             ,'YES'           )          ,DECODE           (              password             ,NULL, 'NONE'             ,'EXTERNAL', 'EXTERNAL'             ,'GLOBAL', 'GLOBAL'             ,'APPLICATION', 'APPLICATION'             ,'PASSWORD'           )      FROM user$     WHERE type# = 0 AND name NOT IN ('PUBLIC', '_NEXT_USER');

--可以发现有2个排除在外.这样默认角色PUBLIC好理解,后面_NEXT_USER一般增加用户后user#会加1(不是角色).

--实在不知道另外1个是什么?