Recently I have been migrating a database from version 10g to 12c. In the functionality testing phase the error ORA-01924 arose. This occurs (in version 12.1) when the 600 roles assigned to a user are exceeded, Oracle has a specific oneoff collected in bug 18934948. But after applying this oneoff, the problem. After doing several tests we saw that it was a problem of lack of privileges. The user who made the mistake He lacked the ability to interact with roles.
In version 10g I did not have any problem with the level of privileges assigned, but in version 12c I needed to add two new permits. It may seem strange the difference of operation between versions, but it really makes sense if you look at the model of continuity. Oracle in the last has accelerated this part of the databases, in general it has been a leader in many aspects, but here has always been one step behind other rdbms such as SQL Server.
In this case giving these two privileges to the user solved the problem;
- drop role
- grant any role
HTH – Antonio NAVARRO.