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:

 

SQL> select count(*) from dba_roles

  2  where role = 'BRIAN'

  3  /

 

  COUNT(*)

----------

         0

 

SQL> select count(*) from dba_users

  2  where username = 'BRIAN'

  3  /

 

  COUNT(*)

----------

         1

 

SQL> create role brian

  2  /

create role brian

            *

ERROR at line 1:

ORA-01921: role name 'BRIAN' conflicts with another

user or role name

 

SQL>