How to connect HR sample user with Oracle Database 18c by Manish Sharma

What’s up Internet? I am Manish from and I
am back with another Oracle Database 18c tutorial. In this video we will learn how to connect
to your database using HR user through SQL*Plus in Oracle Database 18c. All the Oracle 10g or 11g users must be saying,
what’s the big deal in connecting with database! Simply write the
SQLPLUS hr/hr And hit enter. Software will connect you to your database. Right? Let’s do one thing. Let’s hit the enter key and see if this
works with Oracle Database 18c or not. What! We got an error “Invalid Username and password”
How could this be possible? Just in the last video we unlocked the HR
user, didn’t we? Does that mean Oracle Database 18c has some
sort of bug? Relax, there is no bug, everything is correct,
but not complete. Let me explain it to you. Whenever we write a connection string in Oracle
11g style, like this one, the server starts searching the user with which we are trying
to connect to the database inside the container database. For example here we are trying to connect
to our Oracle Database 18c using HR user. On pressing the enter key the server starts
searching this HR user in our container database. But as we learnt in the previous video that
in multi-tenant architecture only the high privileged users like sys and system were
placed inside the container database and other sample schemas such as HR and OE were moved
to the default pluggable database, which we created during the installation. Hence we got this “Invalid Username and
Password” Error. Now you must be asking – Manish, how can
we solve this error and connect to our database? We can easily solve this error just by telling
the server where to find this HR user before it starts searching for it in the container
database. We can do that by specifying the corresponding
service name of the pluggable database into which our user is placed along with the username
and password in the connection string. For example, in our case, we are trying to
connect our Oracle Database 18c using HR user which is placed inside our default pluggable
database whose name is ORCLPDB. The corresponding service name for our Pluggable
Database “ORCLPDB” is also “orclpdb”. We’ve already learnt all this information
in the last video. If you haven’t watched it then I suggest
you to go and watch it right away. Moving on…. Now let me show you, how to properly connect
to your Oracle Database 18c. For the demonstration I am using the user
HR which is a sample schema, you can use whatever user you want. The process will remain the same. Great. Now, as I mentioned earlier that to connect
with our database in Oracle 18c we need to specify the corresponding service name of
the pluggable database into which our user is placed along with the username and password
in the connection string. There are two ways for specifying the service. These are:
1. The basic way and
2. Through using TNS alias. Let’s first see the basic way. It’s slightly complicated as you have to
go through this hustle every single time you want to connect to your database. Anyways
Here we specify the service name through a valid URL. In this URL we specify the domain name and
port number over which our Oracle Server is configured. I have configured my Oracle Database 18c completely
on default settings for the demonstration thus the URL for my pluggable database service
will look something like this. //localhost:1521/ORCLPDB
Localhost is the hostname and 1521 is the port number where my Operating System is hosting
the Oracle 18c server and ORCLPDB is the name of the service for my default pluggable database. Your URL may look different. In case you don’t know your host name and
port number then you should check the “listener.ora” file for the information. The location path of the file must be flashing
on your screen right now. You can also check the description for the
same. Now that we have the URL path for our service,
let’s write the connection string and try to connect to our database 18c using HR user. But first
Let’s get rid of this mess, let’s clear the screen also. Great. Now let’s write the connection string
SQLPLUS HR/[email protected]//localhost:1521/ORCLPDB First write the keyword SQLPLUS as this will
invoke the compiler. Then you have to write the username followed
by a forward slash and then password. In my case the username as well as the password
is HR. Then put @ sign make sure there should not
be a blank space between your password and the @sign. Now, specify the URL for your service. That’s all you have to do. Now let’s press the enter key and see if
we can connect to our database this time or not. Voila! We are successfully connected. You can verify it by writing
SHOW user; This will show you the username through which
you have established a connection. So that’s the first way. Now let’s see the second method which is
using the TNS alias. First of all you have to create a TNS alias
in the TNSNAMES.ORA file. I have demonstrated how to create a TNS entry
in the last video. In the last video we have created a TNS alias
again with the name ORCLPDB in our tnsnames.ora file. Take a look. As you can see, here in this entry we have
done all the necessary settings. We named this entry on the name of our pluggable
database which is ORCLPDB and set the host as localhost and port as 1521. We have also specified the service name “orclpdb”
corresponding to the pluggable database. All the settings have been configured and
saved. Now let’s come back to our command prompt
and see how we can use this TNS entry for connecting to our database. Before we start writing the connection string
let me quickly show you the syntax. Here we are. SQLPLUS username/password @TNS_alias
Connection string will again start with the keyword SQLPLUS followed by the username and
the password both separated by a forward slash, then we have @sign followed by the TNS alias. Hope the syntax is clear to you. Now let’s connect to our database using
the TNS entry that we have created. Here I have opened a fresh command prompt. Now let’s write the connection string. SQLPLUS hr/hr @ ORCLPDB
Here, you can see I am using my HR user for establishing a connection with my database. Also, we have replaced the tedious URL Location
with a simple TNS service name which is ORCLPDB. Let’s press the enter key and see if we
can connect to the database or not. Seems like we have successfully logged onto
our database using the HR user. Great
So in a nutshell, we can say that in a database management software which is built over multi-tenant
architecture like Oracle 12c or 18c we need to specify the name of the service corresponding
to the pluggable database into which the user or say schema is placed. Hope. You will find this tutorial helpful. Stay tuned as in the next tutorial we will
see how to connect with the database using SQL Developer. Meanwhile make sure to give this video a thumbs
up and share it with your friends on your social media. Most importantly, subscribe if you haven’t
already. Thanks for watching, this is Manish from

Daniel Ostrander

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *