Error at line 1 ora 01918 user hr does not exist

SQL*Plus: Release 21.0.0.0.0 - Production on Tue Feb 8 17:33:44 2022
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> alter user hr identified by hr account unlock;
alter user hr identified by hr account unlock
           *
ERROR at line 1:
ORA-01918: user 'HR' does not exist
 
SQL> alter user hr identified by hr;
alter user hr identified by hr
           *
ERROR at line 1:
ORA-01918: user 'HR' does not exist'''

2

Learn to unlock HR user in Oracle DB 19c. Now that Oracle Database 19c has been launched let’s configure it, so that we can start practicing on it. The best part of Oracle Databases is that, they always come with pre-designed, fully functioning sample schemas. And, you can use those sample users to start your journey with Oracle Database.

What are schemas in Oracle Database?

A schema is a collection of logical structures of data, or schema objects. Furthermore a schema is owned by a database user and has the same name as that of the user. Also, each user owns a single schema.

Now you must be having questions in your head like, what are the differences between user and schema or can a schema be created without a user or vice versa?

I think Tom Kyte has answered all these questions in this Ask Tom Thread. Check it out here. If you still have any doubts about Oracle Database Schema, then hit me up On My Facebook. 

Has Oracle removed the sample schemas from Oracle 19c?

Common question you often ask me is – While trying to unlock the HR user, I am getting an error saying, “user HR does not exist”. Does it mean Oracle has removed the HR user from its latest version?

My answer is – No, all the HR schema is still there it’s just that you are searching them at the wrong place.

In multi-tenant architecture, HR users are placed inside the default pluggable database. And by default, the name of that pluggable database is orclpdb (provided you did not change it during the installation).

Now let’s come to our topic which is how to unlock HR user in Oracle 19c?

Unlocking sample accounts like HR in Oracle 19c is a three-step process. These are –

1. Create A TNS service
2. Switch the Container, and
3. Unlock the Account.

Let’s see what these three steps are and how we can unlock an account using them.

Step 1: Create A TNS Service.

TNS stands for Transparent Network Substrate. It’s Oracle Proprietary networking technology. And is used for establishing a peer-to-peer communication between Server and Client.

I have already done a detailed tutorial on TNS which you can check here. Anyways

In order to unlock the sample user in Oracle 19c we first need to create a TNS service for the pluggable database which contains those sample users. However in our case that pluggable database is orclpdb.

To create a TNS service for a database we need some information. Such as –

1. Container ID
2. Service Name

We need the container ID of the database for which we are creating a TNS service. Furthermore we will use this container id to find out our second requirement which is the service name.

Service Name – A service name is a logical representation of a database. A database is represented to clients as a service. Almost, all the time the database and its corresponding service shares the same name.

In Oracle Database a Service can also be defined as a TNS alias used by user to connect with the Database remotely.

Now let’s find out this information.

Find the container ID

To find out the container id of the pluggable database we need to query the v$pdbs view.

COLUMN name FORMAT a20;
SELECT name, con_id FROM v$pdbs;

This select statement will return the name and the container id of all the available pluggable databases.

So, now read the output carefully and find the name and the container ID of your default pluggable database. Which you have created during the installation. And, if you didn’t change it while installing Oracle 19c then its name must be orclpdb (default name of the default pluggable database)

Anyways in my case the name of the pluggable database which holds all my sample users is also orclpdb and its container ID is 3. Consequently, I will be using this information to find out the service name.

What is PDB$SEED?

PDB$SEED is the seed pluggable database created by Oracle Universal installer during the installation. Additionally, this seed pluggable database is used as a template for creating the user defined pluggable databases. And I suggest you not to mess with it. Anyways—

Find the Service Name.

In almost all the cases the database and its service share the same name. But I suggest you check it and be assured.

To find out the service name associated with our pluggable database we need to query the v$active_services view.

SELECT name as “Service Name” FROM v$active_services  WHERE con_id =3;

This statement will return the name of the service associated with the pluggable database whose container id is 3. Which we know is the pluggable database orclpdb.

Create A TNS alias.

There are two ways of creating a TNS alias/service. These are –

1. Using network configuration assistant (NetCA).
2. Manually adding the entry into tnsnames.ora file.

Creating a TNS service using NetCA is always recommended. I have demonstrated a step-wise guide on how to create a TNS entry using Oracle NetCA in my previous tutorial. Click here to watch that. Moreover you can also download this guide in PDF format for free, just fill in the form given below.

Now let me show you how to add a TNS entry manually into tnsnames.ora file.

Locate the tnsnames.ora file.

In order to add a TNS service into tnsnames.ora file you first need to locate it. And you can find your tnsnames.ora file at

%db_home%networkadmin

Inside the admin folder you can find your tnsnames.ora file. However before you do any modifications to it, I suggest you to make a backup copy of it.

Create a TNS service.

Creating a TNS service manually will be a lot easier. Simply copy any TNS service which is already created and paste it at the end of the file. After that you have to modify this entry. These are the changes which you have to do.

Name: Since, we are creating a TNS service for our pluggable database orclpdb, thus we will name our TNS ORCLPDB. Though it is not mandatory, you can name it whatever you want.

Protocol Address: Protocol address is comprised of three things. These are-

1. Hostname
2. Port Number, and
3. Network protocol

Almost, all the time the protocol address of a TNS service is same as the listener of your database. Which means you can check your listener.ora file to find all this information.

You can find listener.ora file in the same directory where your tnsnames.ora file is stored.

 %db_home%networkadmin

Anyways, the default values for the protocol address are –

Hostname: It is set to either localhost or the IP address of the system where your Oracle DB is installed and configured

Port Number: Oracle Database uses 1521 as the default port number for communication on the network.

Protocol: By default, Oracle Database uses TCP Network Protocol for network communication.

Step 1 create a TNS service has been done here.

Reload the listener

You need to reload the listener in order to let all the modifications which you just did settle down.

Therefore, open your command prompt and just write

>>lsnrctl reload

This will reload your listener and your TNS service which you just created all set to be used.

Step 2: Switch The Container.

In multitenant architecture, when you log on to your database using sys user it will connect you using the default container database CDB$ROOT. But as said earlier that all the sample schemas are placed inside the default pluggable database ORCLPDB. Because of that we need to switch the container from cdb$root to orclpdb. 

Here are the steps of switching containers in Oracle 19c. But if you love watching videos then I have done a short video on my Facebook showing the process of switching the containers. And I will embed that video at the end of this section.

Step 2.1: Fire up your SQL*Plus

Open up your command prompt and write –

sqplus / as sysdba

Now you will be presented with your SQL prompt.

Step 2.2: Switch the container

To switch the container, you need to perform an ALTER DDL. Which is –

ALTER SESSION SET container = ORCLPDB; 

In this statement ‘ORCLPDB’ is the name of the pluggable database. And it has all the sample schemas. 

Before we unlock our sample user we just need to make sure that our pluggable database ‘orclpdb’ is open. So let’s check that –

COLUMN name FORMAT a20;
SELECT name, open_mode FROM v$pdbs;

If the result of the above statement is ‘Read Write’ then it means your pluggable database is open and you can perform ALTER USER DDL. If not, then it means the pluggable database is not open.

If that is the case, then we need to open our pluggable database. For that you simply have to write another SQL statement which is –

ALTER PLUGGABLE DATABASE open;

That’s all you have to do. All the settings has been done and we are ready to unlock our sample users.

Step 3: Unlock the HR user in Oracle 19c.

To unlock the HR user in Oracle 19c you can use the old and trusted ALTER USER DDL. Like this

ALTER USER hr IDENTIFIED BY <your_pass> ACCOUNT UNLOCK;

Furthermore, in the above statement replace <your_pass> with the password for your HR user.  On successful execution of the above DDL statement your HR schema will be unlocked.

That is how we unlock HR user in Oracle database 19c. Hope you enjoyed reading. 

Thanks and have a great day!

How to unlock HR schema? [XE]

Unlocking sample HR scheme in versions older than Oracle 12c was very easy. Just execute from the SYS user:

 alter user hr identified by hr account unlock;  

 alter user hr identified by hr account unlock;  

 Error report -  
 ORA-01918: user 'HR' does not exist
 01918. 00000 - "user '%s' does not exist"  



  


                       
  



*Cause:  User does not exist in the system.  
*Action:  Verify the user name is correct.  

But I know, that HR is out there somewhere…

When we log as SYS user, we log into the CDB database. And there is actually no HR schema. The HR schema has been hidden in the pluggable database. To be able to unblock the HR user, we need to connect to the PDB (pluggable) base.

How to do that?

1. Find PDB name

as a SYS user execute the SQL

 SELECT name, con_id, open_mode FROM v$pdbs;  

Out PDB base is XEPDB1.

2. Update tnsnames.ora file

We need to add our PDB database into the tnsnames

We can find TNSNAMES file in the folder:

<ORA_HOME> product18.0.0dbhomeXEnetworkadmin

example:

C:appuserproduct18.0.0dbhomeXEnetworkadmin

We add the connetion to PDB database (enter the name of your database instead of XEPDB1):

  XEPDB1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = admin)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = XEPDB1)

    )

  )


3. Resart the listener.

4. Open XEPDB1

We check with the query from point 1 whether our database is in the READ WRITE mode. If not, we can open it with the command:

 ALTER PLUGGABLE DATABASE ALL OPEN;  

5. Connect to container XEPDB1

As a SYS connect to XEPDB1

 ALTER SESSION SET container=XEPDB1;  

We can reconnect to root usind statement:

 ALTER SESSION SET container=CDB$ROOT;  

but let’s not do that for now, we need to be PDB based.

6. Unlock HR schema

And finally we can unlock our sample HR scheme!

 alter user hr identified by hr account unlock;

 User HR altered.  

7. Connect to HR.

We need to create a connetion to the HR schema. Remember that the database is not on the standard SID, for me it is XE, only on the SID that we created in point 2. So for me it is XEPDB1.

And now we can enjoy our beautiful, new HR scheme

Solved: ORA-01918,ORA-00911 Error User Does not exist
I was trying to drop a user , which was created with junk character

**********Recommended solution is to drop the user via OEM,*******

Below is NOT recommended by oracle, hence it may be risk
============================

Junk character would have been added by additional space/backspace keystroke by user while creating,.
example
========
username «king» while creating a space or backspace key stroke was hit by user in creation user script as junk character

SQL> select username from dba_users where username like ‘king’;

no rows selected

SQL> — Notice the junk character below
SQL> select username from dba_users where username like ‘%king%’;

USERNAME
——————————
king?

SQL>drop user king
ERROR at line 1:
ORA-01918: user ‘king’ does not exist

SQL> drop user king?;
drop user king?
              *
ERROR at line 1:
ORA-00911: invalid character

SQL> drop user «king?»;
drop user «king?»
          *
ERROR at line 1:
ORA-01918: user ‘king?’ does not exist

SQL> — Note : Make sure their are no segments owned by user
SQL> — any way user would have not logged in so for,… but just saying
SQL>      
SQL> select user#,name from sys.user$ where name like ‘king%’;

     USER# NAME
———- ——————————
        96 king?

       
SQL>— Will update the new name in sys.user$ table, and drop the user and recreate correctly. — This is unreommeded as we are not suppose to touch the base,.. it may create logical corruption.
SQL>— Please make sure you DB is backed up if you dont have any other option…

SQL> update sys.user$ set name=’king_new’ where user#=96 and name like ‘king%’;

1 row updated.

SQL> commit;

Commit complete.

SQL> select username from dba_users where username like ‘king_new’;

USERNAME
——————————
king_new

SQL> select username from dba_users where username like ‘king%’
  2  ;

USERNAME
——————————
king_new
SQL> — Note the double quote
SQL> drop user «king_new»;

User dropped.

New create the user again without junk character.

Note :

====
please use the commands carefully before executing in your server 

its advised to test in test server.

Note : 

====
Implementing or trying any of the advice from this blog is at your own risk.