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:
 
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>

No comments: