Archive

Posts Tagged ‘roles’

ORA-01925 maximum of %i enabled roles exceeded

March 17, 2010 Leave a comment

I’ve exceeded the maximum roles enabled for a user.

$ sqplus "/as sysdba"
SQL> show parameter MAX_ENABLED_ROLES
NAME               TYPE    VALUE  
------------------ ------- -----
max_enabled_roles  integer 148

This is a static parameter and can take values from 0 to 148 (http://download.oracle.com/docs/cd/B13789_01/server.101/b10755/initparams118.htm).

Te solution is to increase the MAX_ENABLED_ROLES parameter and warm start the database (shutdown/startup):

  1. shutdown the database
  2. include the parameter in the init.ora file
  3. startup the database

If using spfile, issue this command:

SQL> alter system set max_enabled_roles=130 scope=spfile sid='*';
SQL> shutdown
SQL> startup

If you’re in my case that you have the parameter at it’s maximum value, the solution is to revoke roles from user (for me, the SYSTEM user):

$ sqlplus "/as sysdba"
SQL> select * from dba_role_privs where grantee = 'SYSTEM';
SQL> revoke ... from SYSTEM;

Advertisements
Categories: oracle Tags: ,