Creating an Oracle User if it doesn't already exist

ora-65096: invalid common user or role name
oracle create user
oracle 12c create user
grant role to user oracle
oracle list users
grant dba privileges to user in oracle
grant schema access to user in oracle
oracle create user schema

I am trying to create a script that will create users if they do not already exist.

CREATE USER "Kyle" PROFILE "DEFAULT" IDENTIFIED BY "password" ACCOUNT UNLOCK
WHERE NOT IN  //Also tried 'WHERE NOT EXISTS'
(
    SELECT username FROM all_users WHERE username = 'Kyle'
)

The following error is given:

SQL Error: ORA-00922: missing or invalid option

I was able to do this in SQL Server 2008 by using:

IF NOT EXISTS
(SELECT name FROM master.sys.server_principals
WHERE name = 'Kyle')
BEGIN
    CREATE LOGIN Kyle WITH PASSWORD = 'temppassword' MUST_CHANGE, CHECK_EXPIRATION=ON, CHECK_POLICY=ON
END

Is there a similar way in Oracle to check if a user already exists before attempting to create a new user?


The IF NOT EXISTS syntax available in SQL Server, is not available in Oracle.

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 what all the standard Oracle deployment scripts do.

However, if you really want to check for existence, and only execute if object doesn't exist, thereby avoiding the error, you can code a PL/SQL block. Write a SQL that checks for user existence, and if it doesn't exist, use EXECUTE IMMEDIATE to do CREATE USER from the PL/SQL block.

An example of such a PL/SQL block might be:

declare
userexist integer;
begin
  select count(*) into userexist from dba_users where username='SMITH';
  if (userexist = 0) then
    execute immediate 'create user smith identified by smith';
  end if;
end;
/

Hope that helps.

How to Create Users, Grant Them Privileges, and , So, you've got your shiny, brand new Oracle Database up and running. Once you're in, the basic create user command is: ? Read doesn't. The following example creates a global user. When you create a global user, you can specify the X.509 name that identifies this user at the enterprise directory server: CREATE USER global_user IDENTIFIED GLOBALLY AS 'CN=analyst, OU=division1, O=oracle, C=US' DEFAULT TABLESPACE example QUOTA 5M ON example; Creating a Common User in a CDB


You need to write a pl/sql block. See an example here

You can check if the user exists in the all_users table using some pl/sql code like:

SELECT count(*) INTO v_count_user
FROM all_users
WHERE username = 'Kyle'

and then use v_count_user in an IF condition to conditionally execute the create user statement.

CREATE USER, When you create a user with the CREATE USER statement, the user's privilege domain is empty. To log on to Oracle Database, a user must have the CREATE  Write a SQL that checks for user existence, and if it doesn't exist, use EXECUTE IMMEDIATE to do CREATE USER from the PL/SQL block. An example of such a PL/SQL block might be:


From the previous answers, it is clear that if not exists is not supported in Oracle. To clarify which error(s) are thrown by Oracle when attempting to create an already existing user (and as a bonus, when attempting to drop a non existing user):

drop user foo;
ORA-01918: user 'foo' does not exist

create user existing_user IDENTIFIED BY existing_user;
ORA-01920: user name 'existing_user' conflicts with another user or role name

The statements above were executed on Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

Force drop user in Oracle database – Code Snippets, How do I force a user to drop in Oracle? The IF NOT EXISTS syntax available in SQL Server, is not available in Oracle. 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.


Another way to do it is to create the user in a PL/SQL block and catch the ORA-01920 error. That way, the block does not throw any errors when the user exists.

DECLARE
    sqlStatement varchar2(512);
    user_exists EXCEPTION;
    PRAGMA EXCEPTION_INIT(user_exists, -1920);
BEGIN
    sqlStatement := 'CREATE USER "Kyle" ' ||
       'IDENTIFIED BY "password" ' ||
       'PROFILE "Default" ' ||
       'ACCOUNT UNLOCK';
    EXECUTE IMMEDIATE sqlStatement;
    dbms_output.put_line('  OK: ' || sqlStatement);
EXCEPTION
   WHEN user_exists THEN
     dbms_output.put_line('WARN: ' || sqlStatement);
     dbms_output.put_line('Already exists');
   WHEN OTHERS THEN
     dbms_output.put_line('FAIL: ' || sqlStatement);
     RAISE;
END;
/

CREATE TABLE IF NOT EXISTS on Oracle database, How do you create a table only if it does not exist in Oracle? oracle,oracle11g,createuser , Creating an Oracle User if it doesn't exist already. The IF NOT EXISTS syntax available in SQL Server, is not available in Oracle. 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.


Difference between database vs user vs schema, DB documentation you can find all the error codes you can catch. CREATE USER . Purpose. Use the CREATE USER statement to create and configure a database user, which is an account through which you can log in to the database, and to establish the means by which Oracle Database permits access by the user.


Creating an Oracle User if it doesn't already exist - oracle - iOS, What is the difference between user and schema in Oracle? Creating the Oracle Software Owner and Group. When you install Oracle software, you install it as the user oracle. This allows you to apply certain security and tuning settings to the software without having to change settings for other users on the system.


Schema Only Accounts in Oracle Database 18c, is the set of objects (tables, views, etc.) that belong to that account. But using dbca (database creation assistant) is easier to get started. The IF NOT EXISTS syntax available in SQL Server, is not available in Oracle. 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 what all the standard Oracle deployment scripts do.