1

I am in the process of using Data Pump to setup a copy of an Oracle database from 11.2.0.3.0 on Windows to 11.2.0.4.0 on Oracle Linux. I have had a few issues with invalid objects which I have managed to resolve but I also have a number of indexes with the domidx_opstatus of 'FAILED'.

Attempting to rebuild these indexes results in the error:

ORA-29858: error occurred in the execution of ODCIINDEXALTER routine

ORA-28031: maximum of 150 enabled roles exceeded

This is a large database that I have recently begun working on so would like to avoid removing roles from any users to avoid breaking any fuctionality.

Is there away around this or another way of fixing the indexes?

Improve this question

1 Answer 1

Reset to default
3

The 150 enabled roles limit is hard coded. There is no way around it other than to remove roles from the user or consolidate the existing roles. Ideally you should do the latter for better long term results. See MOS Doc ID 1101035.1 and Doc ID 780749.1 for more details and some tips on how to identify the relevant roles.

Improve this answer
4
  • I have had a look at the dba_role_privs table and the maximum number of roles assigned to any one user is 121 so I don't actually seem to be breaching that limit.
    – djaustin
    Commented Sep 1, 2016 at 8:54
  • Roles can be recursive withing other roles, and these count towards the limit, are you taking that into account?
    – Andrew Brennan
    Commented Sep 1, 2016 at 9:27
  • Also, try using EXCLUDE=ROLE in your import, then add the roles to the created user later.
    – Andrew Brennan
    Commented Sep 1, 2016 at 9:27
  • 1
    Ah that may have been the issue I didn't know about recursive roles. I reimported without roles as you suggested and it seems to have fixed things. Thanks!
    – djaustin
    Commented Sep 2, 2016 at 9:23

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.