ORA-01925: maximum of 30 enabled roles exceeded

If you are maintaining a large number of users within your  database, you will probably be making use of roles to manage user privileges.  If you aren’t, you should be!

Creating a large number of roles as the same user can lead  to issues, because the creator of the role gets the role granted to them by  default. As you continue to create roles, as SYS for example, you can reach the  value specified by the parameter MAX_ENABLED_ROLES. This defaults to 30.

Once the number of roles increases beyond this value +2  (PUBLIC and own roles are granted by default), you will get an error when  attempting to log on: ORA-01925: maximum of 30 enabled roles exceeded.

These are several ways to get around this problem:

1. You can drop the roles that the use has, although this  probably will cause chaos!

2. Increase the parameter in the database, but this requires  a restart

3. Alter the user so that not all of the roles are enabled by default when they log on:

Alter user  testdba default role all except test1;

These are always many ways to work around/solve a problem in  Oracle. Which one you decide to use depends upon your situation and your aim.

Like it, share it...

Category: Database Config

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *