Ошибка ora 01920 - Не ошибается лишь тот, кто ничего не делает!

May 7, 2021

I got ” ORA-01920: user name ‘string’ conflicts with another user or role name ” error in Oracle database.

ORA-01920: user name ‘string’ conflicts with another user or role name

Details of error are as follows.

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.

user name ‘string’ conflicts with another user or role name

This ORA-01920 error is related to attempt creating a user that already exists or attempting to assign a role that has already been assigned.

To solve this error, Specify a different user name.

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

1,553 views last month, 6 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA, Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].- -Oracle DBA, SQL Server DBA, APPS DBA, Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için [email protected] a mail atabilirsiniz.

Источник

I have created a script that creates Oracle users and grants them roles. I am unable to find a way to ignore the «user exists» error:

ORA-01920: user name » conflicts with another user or role name.

I understand that when the script is ran, it is possible that the user already exists, but I want to ignore any returned errors. Is this possible?

My Oracle code:

CREATE USER "John" PROFILE "DEFAULT" IDENTIFIED BY "temppassword" ACCOUNT UNLOCK;

Edit:
This question is not asking how to create a user if it doesn’t exist. This question is asking how to ignore «the user exists» error. According to a previously asked question, the top answer stated

In general, Oracle scripts simply execute the CREATE statement, and if
the object already exist, you’ll get an error indicating that, which
you can ignore. This is whaat all the standard Oracle deployment
scripts do.

asked Jun 10, 2015 at 19:32

Kyle WilliamsonKyle Williamson

2,2216 gold badges43 silver badges74 bronze badges

It isn’t clear how you’re running your script, but assuming its via SQL*Plus you can modify the behaviour when an error is encountered with the whenever sqlerror command.

If your script is setting that to exit at the moment, or you’re picking that up from a startup script (login.sql, glogin.sql) you can change it back, or modify it temporarily:

...
-- do not stop on error
WHENEVER SQLERROR CONTINUE;
CREATE USER "John" PROFILE "DEFAULT" IDENTIFIED BY "temppassword" ACCOUNT UNLOCK;
-- to stop when later errors are encountered
WHENEVER SQLERROR EXIT FAILURE;
ALTER USER ...

You’ll still see the ORA-01920 in the output but it will continue on to execute the next statement. This pattern is also useful for a protective drop of a schema object before attempting to create it.

answered Jun 11, 2015 at 13:24

Alex PooleAlex Poole

183k11 gold badges178 silver badges313 bronze badges

Why can’t you find if the user exists first?

SELECT COUNT(*) 
INTO V_count 
from ALL_USERS 
where username = 'YourUserName'

IF v_count = 0 THEN
  --create the user
  --execute the grants
ELSE
  ---log that the user already exists
END IF;

answered Jun 10, 2015 at 19:46

kevinskiokevinskio

4,4331 gold badge21 silver badges36 bronze badges

SET SERVEROUTPUT ON;
    DECLARE
  TYPE t_list IS TABLE OF VARCHAR2 (30);
  l_list t_list := t_list ('X0', 'X1', 'X2');
  e_user_already_exists EXCEPTION;
  PRAGMA EXCEPTION_INIT (e_user_already_exists, -1920);
BEGIN
  FOR l_iterator IN 1 .. l_list.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE ('Creating user ' || l_list (l_iterator));
    BEGIN
      EXECUTE IMMEDIATE 'CREATE USER "' || l_list (l_iterator) || '" PROFILE DEFAULT IDENTIFIED BY "WELCOME" ACCOUNT UNLOCK';
      EXECUTE IMMEDIATE 'GRANT SOME_APPLICATION_ROLE TO ' || l_list (l_iterator);
    EXCEPTION
      WHEN e_user_already_exists THEN
        DBMS_OUTPUT.PUT_LINE ('User exists, ignored');
      WHEN OTHERS THEN
        RAISE;
    END;
  END LOOP;
END;
/

answered Jun 10, 2015 at 23:48

TeamDittoTeamDitto

5071 gold badge6 silver badges18 bronze badges

Источник

Oracle Error Message

ORA-01920: user name ‘string’ conflicts with another user or role name

Reason for the Error

There is already a user or role with that name.

Solution

Specify a different user name.

Источник

The ORA-01920 error message indicates that the user name or role that you are trying to assign already exists. The name of the new user or role must be unique. You can resolve this issue by using a different name for the new user or role. Usually, the error message will appear if you are trying to assign a role to an existing user. For more information, see the ORA-01920 troubleshooting guide.

In this error message, the Oracle database server notices that there is already an existing user or role with the same name. It tries to create a new one but fails because of the conflict. In this case, you should try to rename the current users or roles. If the problem persists, try deleting the old ones and creating new ones. You will have to change the names of all users and roles in order to make sure that the new users are not already present.

When you have created a new user or role, you will see a message stating that you are not allowed to change the original name of the other user or role. The error message will tell you that a certain user or role already exists in the database. This is a very common problem and it is recommended that you rename the existing ones. When the names of two users or roles conflict, Oracle will not create a new one.

The error message will show the user name that is being used by another user or role. It will indicate that the new person’s name or role already exists in the database. Therefore, you must remove it before creating a new one. You can then proceed with the process of creating the new user or role. You should also check for a database error log to see if the problem still persists.

The ORA-01920 error message is a warning that the newly created user or role name does not match the existing one. In most cases, this error message is caused by the fact that the named person or role already exists in the database. This error message occurs when a user or a group already exists in a database. In this case, the new user or role cannot be created because it is too similar to the other user or its role.

Upon creating the database, the ORA-01920 error is triggered because the user or role name matches an existing one. The user name or role that you want to create must be unique. Otherwise, the database will not work. However, you can train yourself to become an expert in Oracle. When you complete an Oracle training course, you will be able to develop a wide range of skills, including how to use the system.

If the name of your user or role is similar to another, you might be seeing an ORA-01920 error. This error means that the existing user or role name already exists in the database. Consequently, you must change the name of the existing user or role. You must first change the user’s database password. If it does not, you should delete it and create a new one.

When you create a new user or role, you must ensure that it does not conflict with any other user or role name. A database name conflict occurs if the same username or role name is already in use. The problem occurs when the existing user or the identity of the other user or the role is duplicated. This error is caused by an incorrectly created user or role. A better solution is to make a backup of the existing database before you start working with it.

An ORA-01920 error message is caused by the fact that the user or role has an already-existing name. If a user or role already exists, it will be impossible to add a new one. To resolve the conflict, you should change the existing names. You can do this by modifying the table’s permissions and database security. A second ORA-01920 error code has the same meaning.

Visit the rest of the site for more useful articles!

Источник

Symptom

You are executing an upgrade with SUM and it fails in the «Create the shadow database schema» step.
When it fails it indicates a DMT.CREATE_SCHEMA.LOG log file, which contains error messages similar to the following:

<timestamp> [Info ]: CREATE USER $(SCHEMA) IDENTIFIED BY «$(PASSWORD)» DEFAULT TABLESPACE $(TABLE_SPACE) TEMPORARY TABLESPACE PSAPTEMP GRANT SAPCONN, UNLIMITED TABLESPACE TO $(SCHEMA)
<timestamp> [Info ]: Generating statements has finished.
<timestamp> [Info ]: Executes statement list:
<timestamp> [Info ]: Connection to SYSTEM has been established.
<timestamp> [Info ]: Error while executing statement.
<timestamp> [Info ]: ORA-01920: user name ‘<username>’ conflicts with another user or role name

<timestamp> [Error ]: Error while executing statement.

<timestamp> [Info ]: Statement execution has finished.

<timestamp> [Error ]: Error while executing statement list.

<timestamp> [Info ]: Connection has been closed.

<timestamp> [Info ]: Could not execute command execute.

<timestamp> [Info ]: Could not execute command runhandler.

<timestamp> [Info ]: Start time: <timestamp>

<timestamp> [Info ]: Duration : <duration>

<timestamp> [Error ]: Error during execution.

<timestamp> [Error ]: java.sql.SQLException: ORA-01920: user name ‘<username>’ conflicts with another user or role name

<timestamp> [Error ]: SQL Exception code 1920: ORA-01920: user name ‘<username>’ conflicts with another user or role name

<timestamp> [Error ]: Could not execute the list of statements.

<timestamp> [Error ]: Could not execute DMT command runhandler. See previous messages.

The user mentioned in the ORA-01920 error does not own any database object:

SQL> select owner, object_name, object_type from dba_objects where owner=upper(‘<username>’);

no rows selected

Environment

SAP Systems based on SAP NetWeaver
Oracle Database

Product

SAP NetWeaver all versions

Keywords

CREATE_SCHEMA, CREATE, SCHEMA, upgrade , KBA , BC-DB-ORA , Oracle , Problem

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP for Me (Login
required).

Search for additional results

Visit SAP Support Portal’s SAP Notes and KBA Search.

Источник

ORA-01920: user name ‘string’ conflicts with another user or role name

user name ‘string’ conflicts with another user or role name

Do you want to learn Oracle Database for Beginners, then read the following articles.

About Mehmet Salih Deveci

Oracle Error Message

Reason for the Error

Solution

Related Posts

Symptom

Environment

Product

Keywords

About this page

Search for additional results