MAX_ENABLED_ROLES
The following procedure will fix the error 'max enabled roles exceeded'.


Apparently, the DEFAULT ROLE for the SYSTEM user is ALL. This means that every time we create a new role, the SYSTEM user is getting that role ENABLED and then we are exceeding the limit imposed by MAX_ENABLED_ROLES.

1.) To change this, you do this:
a) In SQL*Plus, connect as the SYSTEM user

b) Issue this command to see the list of enabled roles: SELECT * FROM SESSION_ROLES;

c) Issue this command to change the SYSTEM user: ALTER USER SYSTEM DEFAULT ROLE DBA;

d) To 'activate' the change, you must login as the SYSTEM user: CONNECT SYSTEM

e) Issue this command to see the list of enabled roles: SELECT * FROM SESSION_ROLES; The list should be a whole lot shorter!

2.) The next step is to change the MAX_ENABLED_ROLES to something more reasonable, like 30. To determine what is reasonable, do this:
a.) Connect as SYS. Run SELECT * FROM SESSION_ROLES;. How many roles are enabled? On my laptop it is 20.

b.) Connect as SYSTEM. Run SELECT * FROM SESSION_ROLES;. How many roles are enabled? On my laptop it is 7.

c.) Connect as CampaignMax Owner. Run SELECT * FROM SESSION_ROLES;. How many roles are enabled? On my laptop it is 10.

d.) Connect as CampaignMax User. Run SELECT * FROM SESSION_ROLES;. How many roles are enabled? On my laptop it is 8.

e.) I picked 30 as 'reasonable' because it was higher than any of my users.

f.) Make sure no users are connected.

g.) Cycle the Oracle Service so the new setting goes into effect.