This discussion is archived
11 Replies Latest reply: Jan 20, 2013 9:57 AM by EdStevens RSS

Error ORA-01920 (conflicts whit another user) but user doesn't exist

985859 Newbie
Currently Being Moderated
I have a problem, of course! (Excuse me for my english.)


I have a DB 10g and exported 4 users from there. I imported in a new DB in a specific order: User1, user2, user3, user4 because it had synonyms, etc.

The User2 has several synonym for tables from User1. Cause a export error I had to drop the User1, but I didn't drop with cascade.
When I attempted create the User1 again I got an error:
ORA-01920: user name User1 conflicts with another user or role name
but this user doesn't exist anymore!
But If I query in dba_users table the User1 appears.
Concluding, I can't create the User1 again and I can't really drop the User1.
What happenned?

I need use the same user name.

Thanks

Edited by: 982856 on Jan 18, 2013 7:04 PM
  • 1. Re: Error ORA-01920 (conflicts whit another user) but user doesn't exist
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl post exact OS and database versions. Is there a reason you cannot drop user1 with cascade option ?

    HTH
    Srini
  • 2. Re: Error ORA-01920 (conflicts whit another user) but user doesn't exist
    TSharma-Oracle Guru
    Currently Being Moderated
    Are you using conventional export and import(exp and imp) or data pump (expdp and impdp)? Please let us know.
  • 3. Re: Error ORA-01920 (conflicts whit another user) but user doesn't exist
    DB Newbie
    Currently Being Moderated
    1.kill the user1 who was using
    2.Then try to drop the user1 with cascade option.
    3.otherwise try to give DBA privs to user1 and then try.
    4.check wether you are connecting to the corresponding Database.
    5.check the Db_link
    --------------------------------------------------------
    1.the problem occured on Production or test DB?
    2.clearly ask your question like what os,oracle version?
    3.the problem occur while export or import?

    regards,
    db
  • 4. Re: Error ORA-01920 (conflicts whit another user) but user doesn't exist
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    ORA-01920:user name 'string' conflicts with another user or role name
    Cause:There is already a user or role with that name.
    Action:Specify a different user name.


    Check
    dba_roles
    dba_users
  • 5. Re: Error ORA-01920 (conflicts whit another user) but user doesn't exist
    EdStevens Guru
    Currently Being Moderated
    982856 wrote:
    I have a problem, of course! (Excuse me for my english.)


    I have a DB 10g and exported 4 users from there. I imported in a new DB in a specific order: User1, user2, user3, user4 because it had synonyms, etc.

    The User2 has several synonym for tables from User1. Cause a export error I had to drop the User1, but I didn't drop with cascade.
    If USER1 owned objects, and you didn't use the 'with cascade' option, then you didn't drop user1. You may have issued the command, but it would have failed.
    When I attempted create the User1 again I got an error:
    ORA-01920: user name User1 conflicts with another user or role name
    Because your DROP (without CASCADE) failed ?

    but this user doesn't exist anymore!
    But If I query in dba_users table the User1 appears.
    If DBA_USERS says user1 exists, and you assert that the user doesn't exist, then either you are mistaken or oracle has a very serious and fundamental bug. Would you like to place a bet on which is true?
    Concluding, I can't create the User1 again and I can't really drop the User1.
    What happenned?

    I need use the same user name.

    Thanks

    Edited by: 982856 on Jan 18, 2013 7:04 PM
    SQL> create user fubar identified by fubar;
    
    User created.
    
    SQL> grant unlimited tablespace to fubar;
    
    Grant succeeded.
    
    SQL> create table fubar.test1 (bdate date);
    
    Table created.
    
    SQL> drop user fubar;
    drop user fubar
    *
    ERROR at line 1:
    ORA-01922: CASCADE must be specified to drop 'FUBAR'
    
    
    SQL> select username from dba_users where username='FUBAR';
    
    USERNAME
    ------------------------------
    FUBAR
    
    SQL> drop user fubar cascade;
    
    User dropped.
    
    SQL> select username from dba_users where username='FUBAR';
    
    no rows selected
    
    SQL> exit
  • 6. Re: Error ORA-01920 (conflicts whit another user) but user doesn't exist
    985859 Newbie
    Currently Being Moderated
    982856 wrote:
    I have a problem, of course! (Excuse me for my english.)


    I have a DB 10g and exported 4 users from there. I imported in a new DB in a specific order: User1, user2, user3, user4 because it had synonyms, etc.

    The User2 has several synonym for tables from User1. Cause a export error I had to drop the User1, but I didn't drop with cascade.
    When I attempted create the User1 again I got an error:
    ORA-01920: user name User1 conflicts with another user or role name
    but this user doesn't exist anymore!
    But If I query in dba_users table the User1 appears.
    Concluding, I can't create the User1 again and I can't really drop the User1.
    What happenned?

    I need use the same user name.

    Thanks
    Thank you all for your answers

    Here's the info:

    Machine DB Export:
    Windows XP SP3
    Oracle DB 10g Release 10.2.0.1.0
    Test DB

    Machine DB Import:
    SUSE Linux Enterprise Server 11 (x86_64) VERSION = 11 PATCHLEVEL = 2
    Oracle DB 11g Release 11.2.0.3.0
    New Production DB

    I'm use export and import (exp and imp). The trouble is post-import, when I dropped user1 the first time (and the only one).

    Really don't remember if I did drop cascade (sorry), I "guess". But no doubt that the drop failed.

    ---
    This is that I'm trying do.

    *1)* I enter with user System

    *2)* Try drop user1
    SQL> drop user user1 cascade;
    drop user user1 cascade
    *
    ERROR at line 1:
    ORA-01918: user 'USER1' does not exist

    *3)* Try create user1
    SQL> CREATE USER "user1" IDENTIFIED BY "user1" DEFAULT TABLESPACE "USER_CONF" TEMPORARY TABLESPACE "USER_TEMP";
    CREATE USER "user1" IDENTIFIED BY "user1" DEFAULT TABLESPACE "USER_CONF" TEMPORARY TABLESPACE "USER_TEMP";
    *
    ERROR at line 1:
    ORA-01920: user name 'user1' conflicts with another user or role name

    *4)* Consulted table dba_users, and user1 exists yet
    USERNAME USER_ID ACCOUNT_STATUS EXPIRY_DA DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED PROFILE INITIAL_RSRC_CONSUMER_GROUP PASSWORD E AUTHENTI
    user1 95 OPEN 18-JAN-14 USER_CONF USER_TEMP 18-JAN-13 DEFAULT DEFAULT_CONSUMER_GROUP
    10G 11G N PASSWORD
    ---

    *5)* Before I tried several things that doesn't work
    - Drop the other users and create user1 again (show error ORA-01920)
    - Revoke grants to user1 (not exists therefore not revoke)
    - I did GRANT "DBA" TO "user1"; and "Grant succeeded", but still says that user1 doesn't exist when I try drop it
    - I did Create table user1.test , but said the same
    - I dropped tablespace USER_CONF (where use1 is located), created again but user1 still appears in dba_users


    Thanks again

    Edited by: 982856 on Jan 19, 2013 9:41 PM
  • 7. Re: Error ORA-01920 (conflicts whit another user) but user doesn't exist
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    >
    ...
    3) Try create user1
    SQL> CREATE USER "user1" IDENTIFIED BY "user1" DEFAULT TABLESPACE "USER_CONF" TEMPORARY TABLESPACE "USER_TEMP";
    CREATE USER "user1" IDENTIFIED BY "user1" DEFAULT TABLESPACE "USER_CONF" TEMPORARY TABLESPACE "USER_TEMP";
    *
    ERROR at line 1:
    ORA-01920: user name 'user1' conflicts with another user or role name
    ...
    >

    When you put something in quotes in Oracle, it is taken literally - i.e. it becomes case sensitive. You created a user account named user1, which is different from a account named USER1. So your drop statement needs to be
    SQL> drop user "user1" cascade;
    http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements008.htm#i27570

    HTH
    Srini
  • 8. Re: Error ORA-01920 (conflicts whit another user) but user doesn't exist
    EdStevens Guru
    Currently Being Moderated
    982856 wrote:
    982856 wrote:
    I have a problem, of course! (Excuse me for my english.)


    I have a DB 10g and exported 4 users from there. I imported in a new DB in a specific order: User1, user2, user3, user4 because it had synonyms, etc.

    The User2 has several synonym for tables from User1. Cause a export error I had to drop the User1, but I didn't drop with cascade.
    When I attempted create the User1 again I got an error:
    ORA-01920: user name User1 conflicts with another user or role name
    but this user doesn't exist anymore!
    But If I query in dba_users table the User1 appears.
    Concluding, I can't create the User1 again and I can't really drop the User1.
    What happenned?

    I need use the same user name.

    Thanks
    Thank you all for your answers

    Here's the info:

    Machine DB Export:
    Windows XP SP3
    Oracle DB 10g Release 10.2.0.1.0
    Test DB

    Machine DB Import:
    SUSE Linux Enterprise Server 11 (x86_64) VERSION = 11 PATCHLEVEL = 2
    Oracle DB 11g Release 11.2.0.3.0
    New Production DB

    I'm use export and import (exp and imp). The trouble is post-import, when I dropped user1 the first time (and the only one).

    Really don't remember if I did drop cascade (sorry), I "guess". But no doubt that the drop failed.

    ---
    This is that I'm trying do.

    *1)* I enter with user System

    *2)* Try drop user1
    SQL> drop user user1 cascade;
    drop user user1 cascade
    *
    ERROR at line 1:
    ORA-01918: user 'USER1' does not exist

    *3)* Try create user1
    SQL> CREATE USER "user1" IDENTIFIED BY "user1" DEFAULT TABLESPACE "USER_CONF" TEMPORARY TABLESPACE "USER_TEMP";
    CREATE USER "user1" IDENTIFIED BY "user1" DEFAULT TABLESPACE "USER_CONF" TEMPORARY TABLESPACE "USER_TEMP";
    *
    ERROR at line 1:
    ORA-01920: user name 'user1' conflicts with another user or role name

    *4)* Consulted table dba_users, and user1 exists yet
    USERNAME USER_ID ACCOUNT_STATUS EXPIRY_DA DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED PROFILE INITIAL_RSRC_CONSUMER_GROUP PASSWORD E AUTHENTI
    user1 95 OPEN 18-JAN-14 USER_CONF USER_TEMP 18-JAN-13 DEFAULT DEFAULT_CONSUMER_GROUP
    10G 11G N PASSWORD
    ---

    *5)* Before I tried several things that doesn't work
    - Drop the other users and create user1 again (show error ORA-01920)
    - Revoke grants to user1 (not exists therefore not revoke)
    - I did GRANT "DBA" TO "user1"; and "Grant succeeded", but still says that user1 doesn't exist when I try drop it
    - I did Create table user1.test , but said the same
    - I dropped tablespace USER_CONF (where use1 is located), created again but user1 still appears in dba_users


    Thanks again

    Edited by: 982856 on Jan 19, 2013 9:41 PM
    I think srini has your problem, with the case sensitivity. Since Oracle defaults all object names (and in this sense, a user is an object) to upper-case, creating things in lower-case by enclosing their names in quotes is nothing short of madness. As your own experience now shows.

    There is one other possibility .. you keep focusing on the user but have not addressed the possibility of the existence of a role by the same name ....

    But even if you find a role, you still should get rid of the quotes and the mixed case ....
  • 9. Re: Error ORA-01920 (conflicts whit another user) but user doesn't exist
    985859 Newbie
    Currently Being Moderated
    >

    >
    When you put something in quotes in Oracle, it is taken literally - i.e. it becomes case sensitive. You created a user account named user1, which is different from a account named USER1. So your drop statement needs to be
    SQL> drop user "user1" cascade;
    http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements008.htm#i27570

    HTH
    Srini
    IT'S WORKED!!!!! :D Was that!
    SQL> drop user "user1" cascade;
    
    User dropped.
    
    SQL> CREATE USER "user1" IDENTIFIED BY "user1" DEFAULT TABLESPACE "USER_CONF" TEMPORARY TABLESPACE "USER_TEMP" 
    
    User created
    Thank you very much Srini.
    Thanks to all for the help.

    Greetings
  • 10. Re: Error ORA-01920 (conflicts whit another user) but user doesn't exist
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    As Ed noted above (and also noted in the link I posted), it is not recommended to use quotes around object names, as it tends to cause too many headaches (as you have experienced !)

    HTH
    Srini
  • 11. Re: Error ORA-01920 (conflicts whit another user) but user doesn't exist
    EdStevens Guru
    Currently Being Moderated
    982856 wrote:
    >
    When you put something in quotes in Oracle, it is taken literally - i.e. it becomes case sensitive. You created a user account named user1, which is different from a account named USER1. So your drop statement needs to be
    SQL> drop user "user1" cascade;
    http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements008.htm#i27570

    HTH
    Srini
    IT'S WORKED!!!!! :D Was that!
    SQL> drop user "user1" cascade;
    
    User dropped.
    
    SQL> CREATE USER "user1" IDENTIFIED BY "user1" DEFAULT TABLESPACE "USER_CONF" TEMPORARY TABLESPACE "USER_TEMP" 
    
    User created
    Thank you very much Srini.
    Thanks to all for the help.

    Greetings
    but by enclosing your username in quotes in the CREATE statement, you just recreated the same headache.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points