11 Replies Latest reply: Jan 20, 2013 11:57 AM by EdStevens RSS

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

    985859
      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
          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
            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
              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
                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
                  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
                    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
                      >
                      ...
                      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
                        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
                          >

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