1

I can't login - and receive the error code above. How do I fix this?

2
  • Offtopic. not a programming question. This is db/server administration. Try the DBA site.
    – Marc B
    Commented Nov 25, 2015 at 19:06
  • Cheers - sorry about that. ps. I was solving a programming problem - my automated tests wouldn't work because I had this error. I came to stackoverflow first. Hopefully others will find the migrated post. Commented Nov 26, 2015 at 12:21

2 Answers 2

1

to let the user connect you need revoke some roles from the user

see discussion about here: AskTom

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1565923300346644029

the discussion about parameter MAX_ENABLED_ROLES that used to limit max count of roles to 150 roles. the parameter is marked as deprecated but as the discussion says it still had some value for limit

It is deprecated because they are currently maxing it out (just like maxtrans is deprecated - but still "exists", it is 255 no matter what you set it to) MAX_ENABLED_ROLES

Range of values 0 to 148

deprecated

MAX_ENABLED_ROLES specifies the maximum number of database roles that users can enable, including roles contained within other roles. The actual number of roles that users can enable is 2 plus the value of MAX_ENABLED_ROLES, because each user has two additional roles, PUBLIC and the user's own role.

0

First, login as

SYSTEM@databasename AS SYSDBA

Then

SELECT * FROM DBA_ROLES;

Then for each role you don't want...

DROP ROLE <rolename>;

To drop roles.

If you have some pattern to the offending roles, you can use the following anonymous loop to clean those up. Imagine you had 50 roles with the word GILES in them which were generated from some erroneous SQL routine:

BEGIN
  FOR c IN ( SELECT ROLE FROM DBA_ROLES WHERE ROLE LIKE '%GILES%' )
  LOOP
    EXECUTE IMMEDIATE 'DROP ROLE ' || c.ROLE;
  END LOOP;
END;
/

Don't ask me how to re-architect your roles, I'm no expert. I just had the same issue where some automated tests were creating roles but not dropping them.

I believe you can't alter the limit so discussions about the limit are pointless.

As Justin_Cave mentions, you can also revoke roles from users to deactivate them or make the role 'non default' which effectively reduces the active role count, and allows you to retain the role for future use.

4
  • This works if you happen to have a script that creates roles, that doesn't clean up after itself, and where you don't need the roles any longer. In the general case, though, you'd want to revoke the role from the user (or make the role non-default). That lets you keep the role (which is generally still useful). Commented Nov 25, 2015 at 18:41
  • Useful addition. Yes - you're right - if the roles are still useful, it's more about which roles are active. Commented Nov 25, 2015 at 18:45
  • -1, That is really a bad advice to drop roles to resolve this error. revoke the roles form the user that has the problem. If you have roles that where created erroneoulsy thatn drop them, even if you don't get a maximum-enabled-roles error. Besides that, initially SYSTEM does not has SYSDAB privileges and I don't think it is reasonable to grant this to it. If SYSTEM does not have SYSDBA privileges then your login will not work.
    – miracle173
    Commented Nov 26, 2015 at 9:20
  • Miracle173 - I had just installed oracle server 11gr2 for this test and my SYSTEM user had SYSDBA privs by default. I don't think this is bad advice at all - if you have roles you do not use or need, the DBA should be dropping them, that's what I've said to do, and that fixed this particular problem in this scenario. I'm not saying there aren't other solutions. Commented Nov 26, 2015 at 12:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.