Home » RDBMS Server » Server Administration » Max Role problem(ORA-28031: maximum of 148 enabled roles exceeded) (Oracle10g)
Max Role problem(ORA-28031: maximum of 148 enabled roles exceeded) [message #338511] Tue, 05 August 2008 04:56 Go to next message
dhimanvipin
Messages: 37
Registered: August 2008
Location: Gurgaon
Member
Hi have a SID in oracle10g databse under which i create my oracle users.
I already have more than 50 users in my SID.
Now when i try to loing through System its saying "ORA-28031: maximum of 148 enabled roles exceeded)".
Neither i can access this SID nor i can delete any role from this but i can access the existing users i have creating under this SID.
Please suggest how can i tackle this problem.
Thanx in advance.
Re: Max Role problem(ORA-28031: maximum of 148 enabled roles exceeded) [message #338515 is a reply to message #338511] Tue, 05 August 2008 05:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
MAX_ENABLED_ROLES

Regards
Michel
Re: Max Role problem(ORA-28031: maximum of 148 enabled roles exceeded) [message #338521 is a reply to message #338515] Tue, 05 August 2008 05:11 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

MAX_ENABLED_ROLES

This parameter is deprecated. It is retained for backward compatibility only.
Re: Max Role problem(ORA-28031: maximum of 148 enabled roles exceeded) [message #338525 is a reply to message #338515] Tue, 05 August 2008 05:13 Go to previous messageGo to next message
dhimanvipin
Messages: 37
Registered: August 2008
Location: Gurgaon
Member
Thanx Michel.
Can u suggest me how would i change this parameter?
I am not able to login to this SID.
Please suggest
Re: Max Role problem(ORA-28031: maximum of 148 enabled roles exceeded) [message #338526 is a reply to message #338525] Tue, 05 August 2008 05:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can always connect as sysdba.
Depending on your configuration file either use "alter system" to modify spfile or "vi" to modify init.ora and restart the database.

Regards
Michel
Re: Max Role problem(ORA-28031: maximum of 148 enabled roles exceeded) [message #338534 is a reply to message #338526] Tue, 05 August 2008 05:28 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

If you get the error
ORA-28031: maximum of 148 enabled roles exceeded
Which means that you have hit the maximum limit.

So what's the use of trying to change it ?

I wonder what user needs more than 148+2 roles ?

You have to "re-organize" your role structure

[Updated on: Tue, 05 August 2008 05:29]

Report message to a moderator

Re: Max Role problem(ORA-28031: maximum of 148 enabled roles exceeded) [message #338535 is a reply to message #338526] Tue, 05 August 2008 05:31 Go to previous messageGo to next message
dhimanvipin
Messages: 37
Registered: August 2008
Location: Gurgaon
Member
Hi Michel,
I am new to this admin side.
I didn't find any entry related to max_enable_roles in ora file.
Pls see the attached ora file for this SID.

Thanx
Vipin
Re: Max Role problem(ORA-28031: maximum of 148 enabled roles exceeded) [message #338539 is a reply to message #338534] Tue, 05 August 2008 05:33 Go to previous messageGo to next message
dhimanvipin
Messages: 37
Registered: August 2008
Location: Gurgaon
Member
Thanx sir.
We have certain scripts for importing the DB dumps. for each imported dumps its create four users and same no of roles.
I guess thats the main reason we are falling short of users.
We can not do much about these scripts.
Pls suggest how would i change this parameter.
Re: Max Role problem(ORA-28031: maximum of 148 enabled roles exceeded) [message #338552 is a reply to message #338535] Tue, 05 August 2008 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add it to the file if it does exist.

Regards
Michel
Re: Max Role problem(ORA-28031: maximum of 148 enabled roles exceeded) [message #338569 is a reply to message #338552] Tue, 05 August 2008 06:38 Go to previous messageGo to next message
dhimanvipin
Messages: 37
Registered: August 2008
Location: Gurgaon
Member
Hi Michel,
I tried to do as u suggested(set MAX_ENABLED_ROLES=200) but could not get anything i mean still the same error.
what value i supposed to set please suggest.

Re: Max Role problem(ORA-28031: maximum of 148 enabled roles exceeded) [message #338570 is a reply to message #338569] Tue, 05 August 2008 06:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you restart the database?

Regards
Michel
Re: Max Role problem(ORA-28031: maximum of 148 enabled roles exceeded) [message #338575 is a reply to message #338570] Tue, 05 August 2008 06:52 Go to previous messageGo to next message
dhimanvipin
Messages: 37
Registered: August 2008
Location: Gurgaon
Member
Michel,
I re-started this oracle service related to this SID.

Thanx
Vipin
Re: Max Role problem(ORA-28031: maximum of 148 enabled roles exceeded) [message #338598 is a reply to message #338575] Tue, 05 August 2008 07:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
post
"show parameter role"
"select startup_time from v$instance;"

Regards
Michel
Re: Max Role problem(ORA-28031: maximum of 148 enabled roles exceeded) [message #338703 is a reply to message #338598] Tue, 05 August 2008 13:45 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
what is the database version & on which o/s it is ?
Re: Max Role problem(ORA-28031: maximum of 148 enabled roles exceeded) [message #338824 is a reply to message #338511] Wed, 06 August 2008 01:04 Go to previous messageGo to next message
dhimanvipin
Messages: 37
Registered: August 2008
Location: Gurgaon
Member
Its oracle10g on Windows XP.
Re: Max Role problem(ORA-28031: maximum of 148 enabled roles exceeded) [message #338825 is a reply to message #338824] Wed, 06 August 2008 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what is the result to my questions?

Regards
Michel
Re: Max Role problem(ORA-28031: maximum of 148 enabled roles exceeded) [message #338833 is a reply to message #338825] Wed, 06 August 2008 01:26 Go to previous messageGo to next message
dhimanvipin
Messages: 37
Registered: August 2008
Location: Gurgaon
Member
I have restarted the system itself but still getting the same error.
Re: Max Role problem(ORA-28031: maximum of 148 enabled roles exceeded) [message #338844 is a reply to message #338833] Wed, 06 August 2008 02:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 06 August 2008 08:06
And what is the result to my questions?

Regards
Michel


Michel Cadot wrote on Tue, 05 August 2008 14:25
post
"show parameter role"
"select startup_time from v$instance;"

Regards
Michel


Re: Max Role problem(ORA-28031: maximum of 148 enabled roles exceeded) [message #338868 is a reply to message #338844] Wed, 06 August 2008 02:34 Go to previous messageGo to next message
dhimanvipin
Messages: 37
Registered: August 2008
Location: Gurgaon
Member
As i alredy explained I cann't login through this SID(eisidb).
like when i say system/pwd@SID it gives me max-role problem.
so i cann't run the command as told by you.

I login into some new SID lets say emqa
sql> connect system/pwd@emqa
>> connect sys as sysdba;
>> pwd
now i run urs commands and go the following result;

SQL> show parameter role;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_enabled_roles integer 150
os_roles boolean FALSE
remote_os_roles boolean FALSE
SQL> select startup_time from v$instance;

STARTUP_T
---------
06-AUG-08

I believe these values are for emqa SID not for the eisidb.
Please suggest how would i know the paramter for eisidb(which is not working).
Please correct me if i m wrong.
Re: Max Role problem(ORA-28031: maximum of 148 enabled roles exceeded) [message #338874 is a reply to message #338868] Wed, 06 August 2008 02:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
max_enabled_roles is still at 150, you didn't change anything, it is expected you have the same error.

I don't understand your questions. These values are for the database you are connected to.
"connect sys as sysdba" should be "connect sys@emqa as sysdba" to get the same database than system.

Regards
Michel
Re: Max Role problem(ORA-28031: maximum of 148 enabled roles exceeded) [message #338879 is a reply to message #338874] Wed, 06 August 2008 03:03 Go to previous messageGo to next message
dhimanvipin
Messages: 37
Registered: August 2008
Location: Gurgaon
Member
Hi Michel,
I did following-
connect sys@eisidb as sysdba
show parameter role;

max_enabled_roles integer 150
os_roles boolean FALSE
remote_os_roles boolean FALSE

You are right nothing being changed for max_enabled roles.
I have set the value as 200 in the init.ora file for this SID.
Pls suggest what should i do now??
Re: Max Role problem(ORA-28031: maximum of 148 enabled roles exceeded) [message #338880 is a reply to message #338879] Wed, 06 August 2008 03:06 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

I assume you haven't red my reply, which I gave yesterday.
It will not have any effect, since max is 148+2
Re: Max Role problem(ORA-28031: maximum of 148 enabled roles exceeded) [message #338886 is a reply to message #338880] Wed, 06 August 2008 03:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As per Oracle documentation you cannot exceed 148.
So you have to change your role definitions and/or settings to users.

Regards
Michel
Re: Max Role problem(ORA-28031: maximum of 148 enabled roles exceeded) [message #338897 is a reply to message #338886] Wed, 06 August 2008 03:59 Go to previous messageGo to next message
dhimanvipin
Messages: 37
Registered: August 2008
Location: Gurgaon
Member
Ok.Thanx
I am already exceeded the no now what should I do now?
Re: Max Role problem(ORA-28031: maximum of 148 enabled roles exceeded) [message #338898 is a reply to message #338897] Wed, 06 August 2008 04:01 Go to previous message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

tahpush wrote on Tue, 05 August 2008 12:28
If you get the error
ORA-28031: maximum of 148 enabled roles exceeded
Which means that you have hit the maximum limit.

So what's the use of trying to change it ?

I wonder what user needs more than 148+2 roles ?

You have to "re-organize" your role structure
Previous Topic: db_block_size
Next Topic: Save point Information.
Goto Forum:
  


Current Time: Thu Dec 08 12:50:32 CST 2016

Total time taken to generate the page: 0.05229 seconds