[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个是什么?