Friday, March 14, 2014

ORA-01921

This was tested on Oracle 11.2. If you try to create a role which already exists, you get an ORA-01921:

SQL> create role dba

2 /

create role dba

*

ERROR at line 1:

ORA-01921: role name 'DBA' conflicts with another user

or role name

SQL>

You can check the names of existing roles by looking in DBA_ROLES something like this:

SQL> select count(*) from dba_roles

2 where role = 'DBA'

3 /

COUNT(*)

----------

1

SQL>

As the error message suggests, roles cannot match existing user names either. In the example below, there is no role called BRIAN. However, there is a user called BRIAN so you cannot create a role called BRIAN: