OraFAQ Forum: Server Administration » ORA-28031 Maximum of 148 roles enabled roles exeeded

Home » RDBMS Server » Server Administration » ORA-28031 Maximum of 148 roles enabled roles exeeded (HP-UX 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-28031 Maximum of 148 roles enabled roles exeeded [message #475295]

Tue, 14 September 2010 04:59

madhavamanohar
Messages: 58
Registered: February 2009

Member

HI All,

SQL> connect USER/Pass
ERROR:
ORA-28031: maximum of 148 enabled roles exceeded

Getting the error like this..!

SQL> show parameter max_enable

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

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

and the below query is swoing 137 rows only..!

select granted_role, default_role
from dba_role_privs
where grantee='<your user>'
/

Still am not able to connect to the db

PLease suggest..

Thanks
MAno

Report message to a moderator

Re: ORA-28031 Maximum of 148 roles enabled roles exeeded [message #475309 is a reply to message #475295]

Tue, 14 September 2010 06:02

jesuisantony
Messages: 166
Registered: July 2006
Location: Chennai

Senior Member

Please go through the following link.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams117.htm

The maximum value that can be set is 148. Also check if additional roles are assigned to the existing roles. This can be checked by the query "select * from role_role_privs where role='role_name'". You need to disable some of the enabled roles of the user to get this issue rectified.

Report message to a moderator

Re: ORA-28031 Maximum of 148 roles enabled roles exeeded [message #475315 is a reply to message #475309]

Tue, 14 September 2010 07:08

madhavamanohar
Messages: 58
Registered: February 2009

Member

Hi,

Please find the output of query given by you.

SQL> select * from dba_role_privs where grantee='DBA';

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ --- ---
DBA XDBADMIN NO YES
DBA EXECUTE_CATALOG_ROLE YES YES
DBA DELETE_CATALOG_ROLE YES YES
DBA WM_ADMIN_ROLE NO YES
DBA IMP_FULL_DATABASE NO YES
DBA GATHER_SYSTEM_STATISTICS NO YES
DBA XDBWEBSERVICES NO YES
DBA SELECT_CATALOG_ROLE YES YES
DBA SCHEDULER_ADMIN YES YES
DBA JAVA_DEPLOY NO YES
DBA JAVA_ADMIN NO YES

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ --- ---
DBA OLAP_DBA NO YES
DBA EXP_FULL_DATABASE NO YES

13 rows selected.

Eventhough we have revoked around 10 roles from the user ,still the problem is persisting.

conn user/passwored

ORA-28031: maximum of 148 enabled roles exceeded

Warning: You are no longer connected to ORACLE.

Please advise.

Thnks
Mano

Report message to a moderator

Re: ORA-28031 Maximum of 148 roles enabled roles exeeded [message #475317 is a reply to message #475315]

Tue, 14 September 2010 07:14

cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester

Senior Member

that's not the query jesuisantony told you to run.

Report message to a moderator

Re: ORA-28031 Maximum of 148 roles enabled roles exeeded [message #475321 is a reply to message #475317]

Tue, 14 September 2010 07:20

madhavamanohar
Messages: 58
Registered: February 2009

Member

Same out put...

Toltal cont is showing 126 only but when i tried to connect its shwing 148 roles..!!

SQL> select count(*) from dba_role_privs where GRANTEE='USERNAME';

COUNT(*)
----------
126

SQL>

SQL> select * from role_role_privs where role='DBA';

ROLE GRANTED_ROLE ADM
------------------------------ ------------------------------ ---
DBA XDBADMIN NO
DBA WM_ADMIN_ROLE NO
DBA DELETE_CATALOG_ROLE YES
DBA EXECUTE_CATALOG_ROLE YES
DBA XDBWEBSERVICES NO
DBA GATHER_SYSTEM_STATISTICS NO
DBA IMP_FULL_DATABASE NO
DBA JAVA_DEPLOY NO
DBA SCHEDULER_ADMIN YES
DBA SELECT_CATALOG_ROLE YES
DBA OLAP_DBA NO

ROLE GRANTED_ROLE ADM
------------------------------ ------------------------------ ---
DBA JAVA_ADMIN NO
DBA EXP_FULL_DATABASE NO

Thanks
Mano

Report message to a moderator

Re: ORA-28031 Maximum of 148 roles enabled roles exeeded [message #475322 is a reply to message #475321]

Tue, 14 September 2010 07:28

cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester

Senior Member

What does this give:

SELECT granted_role
FROM dba_role_privs
WHERE grantee = 'USERNAME'
UNION
SELECT granted_ROLE FROM role_role_privs
CONNECT BY PRIOR granted_role = ROLE
START WITH ROLE IN (SELECT granted_role FROM dba_role_privs WHERE grantee = 'USERNAME');

Report message to a moderator

Re: ORA-28031 Maximum of 148 roles enabled roles exeeded [message #475463 is a reply to message #475322]

Wed, 15 September 2010 04:34

madhavamanohar
Messages: 58
Registered: February 2009

Member

Thanks For your help..

It has been resolved Now.

we have revoked some roles from that schema.

Report message to a moderator

Re: ORA-28031 Maximum of 148 roles enabled roles exeeded [message #475469 is a reply to message #475463]

Wed, 15 September 2010 04:39

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If this was as easy as revoke some roles from user, my question is "why the user had these roles?".

Regards
Michel

Report message to a moderator

Re: ORA-28031 Maximum of 148 roles enabled roles exeeded [message #475470 is a reply to message #475463]

Wed, 15 September 2010 04:40

cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester

Senior Member

I would suggest you have too many roles in your system generally. Try consolidating them.

Report message to a moderator

Previous Topic:	SYS_LOB0000 in dba_segments
Next Topic:	ORA-01565: error in identifying file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 23 18:53:58 CST 2025