How to connect with Pluggable Database using SQL Developer by Manish Sharma

How to connect with Pluggable Database using SQL Developer by Manish Sharma


What’s up Internet? I am Manish from RebellionRider.com and I
am back once again with another Oracle Database 18c tutorial. Today we will learn how to connect with pluggable
database using sample user like HR through SQL Developer in Oracle Database 18c. If you love working on command line interface
like me then in the last tutorial we learnt how to connect with pluggable database using
SQL*Plus command line utility. If you want you can watch that. Anyways
Those who don’t know what SQL Developer is, let me give you a quick intro. SQL Developer is a free graphic user interface
developed and distributed by Oracle itself. You can use this software for almost all your
Oracle Database needs. I have done a dedicated video on how to install
and use SQL Developer. You can find the link for that tutorial in
the info card on your screen as well as in the description down below. Before starting this tutorial, I just want
to say that in order to save time we will be using the information and the TNS alias
that we have already created in the Oracle Database 18c tutorial number 2. I highly suggest you to watch that tutorial
beforehand in order to better understand the concepts that we will learn today. That being said let’s not waste much time
and start the tutorial. Once again similar to SQL*Plus CLI, there
are two ways of connecting with a Pluggable Database in Oracle Database 18c.
1. The basic way and
2. Using TNS alias. Let’s learn each of these starting with
the basic method. Launch your SQL Developer, if you haven’t
already. And then come to this connection tab. If for some reason you can’t see this connection
tab on your screen then, go to the View menu and click connections. Once you have your connection tab then click
this big green plus button. This will open up a pop-up window for creating
a new connection. Here we have to do the entire configuration. Let’s do that –
The first field is “Connection Name”. Here you have to give a name to your connection. You can name it whatever you want as it’s
completely user defined. I’ll name my connection “[email protected]”. Fair enough I guess
The second field is “Username”. Here you have to specify the schema name through
which you want to connect with your Oracle Database 18c. Such as for this demonstration I want to use
the HR schema to connect with my database. Thus in my case the username will be HR. You can use whichever schema you want to connect
with your database, it could be a sample schema like the HR or a user defined schema. Please do not get confused between the words
“User” or “Schema”. In Oracle ecosystem both these words are used
as synonyms for each other. Anyways let’s move ahead. The next field is “Password”. As the name suggests in this field you have
to write the password of your schema with which you are connecting with your database. As I am trying to connect with my database
using HR schema thus I will specify the password for HR schema here. Great! Next, please check this “save password”
checkbox. In case you want to specify any color for
your connection then you can use this drop down color selection panel. These four settings are the basic ones which
will remain the same for both type of connections that we will be learning in this video. Now come to this Oracle Section. As you can see SQL Developer has already filled
most of these fields with the default settings. Anyways, let’s explore this section and
see what necessary settings we need here in order to connect with our Oracle Database
18c using a pluggable database. First thing which you will see in this section
is Connection Type. It is a drop down list. From this list you need to select the first
option which is “Basic”. Since right now we are learning the first
way of connecting with a pluggable database which is “The Basic Way” therefore we
selected “Basic” as our connection type. The second thing is “Role”. For this demonstration we are using a sample
schema which is “HR” to connect with our database. HR is a normal user, it doesn’t have any
high privileged role assigned to it thus we will let the role stay set on “Default”. O.K., now come to the next thing. Hostname, which is a text field. In this text field you need to write down
the hostname of your machine. By default it is set on “localhost” & for
the purpose of this demonstration it’s good. So, I won’t change it. Next text field is “Port”. Here you need to specify the port number for
your Oracle Database. The default port number is 1521 which is already
set here. For this demonstration I am good with this
default port number also thus I will not change it. Now you ask, Manish, I don’t know the hostname
and the port number for my Oracle Database 18c, where can I get this information? Well, that is a very good question. In Oracle Database, all the network settings
are saved into the listener.ora file. Thus you can use it to get all your information.>>The location of the listener.ora file must
be flashing on your screen. That being said, let’s move ahead. Next field is SID. SID is a site identifier. It is a unique name for your database instance. In Oracle 10g or 11g the most common name
of database instance used to be ORCL and we used to put that here, if you remember. Now, with the multi-tenant architecture, things
have changed a little bit. SID seems to be losing its charm and services
is slowly coming into the lime light. Thus here we will not be choosing the SID
rather will be going with the “Service Name”. In order to establish a connection with database
through a user in Oracle 18c we need to specify the service name of the pluggable database
into which that user is either created or placed. As for this demonstration we are using HR
user for making a connection thus we will need to specify the service name of its native
pluggable database. We already know that all our sample users
like HR and OE are placed in our default pluggable database whose name is “ORCLPDB” and its
corresponding service name is also “orclpdb”. We will be using this information here. So, in the service name text field we will
write our service name which is orclpdb. Done, That’s all we have to do. Now let’s press the test button and see
if we have successfully created our connection or not. Voila Test status success! Now, just press the save button to save your
connection. So that is the basic way of connecting with
a pluggable database in Oracle Database 18c. I know it is slightly complicated. Suppose you have 20 users created in a pluggable
database, in order to connect with your database using those 20 users you’ll need to do all
these settings individually 20 times for each of those schemas. That’s definitely going to be a headache,
don’t you think so? Anyways
Now the question arises here, don’t we have any less complicated way? My dear friend, I have good news for you. There is a very easy way & that is to connect
with your database using a TNS alias. You can consider TNS alias as an envelope
which contains all these settings which we just did, under a single name. You can create a TNS alias for your pluggable
database by creating an entry into your tnsnames.ora file. Take a look here. I have already created a TNS alias in this
TNSNAMES.ORA file. In this entry I have set “localhost” as
my host name, “1521” as the port number and “orclpdb” as the service name for
the pluggable database. I named this entry “ORCLPDB” after the
name of the pluggable database into which all my sample schemas are placed. I have demonstrated how to create a TNS alias
in detail in Oracle Database 18c tutorial number 2. I suggest you to watch that tutorial and create
a TNS alias for your Database instance right now. Anyways let’s get back to our SQL Developer
and see how to connect with a pluggable database using this TNS alias in Oracle Database 18c. Here we are. As you can see the first three settings which
are Connection Name, username and password will remain the same, so let’s not change
them. Don’t forget to check this save password
checkbox. Now, come back to this Oracle Section. This time we will not be doing all these settings
individually rather we will let the server to do all the dirty work for us. Let’s see how. Come to this connection type and change it
from “Basic” to “TNS”. As soon as you do that things will change. See, that’s what I was talking about. Anyways, let’s move ahead. Next setting is role, let it stay on “default”. Now let’s come to the next setting which
is “Network Alias” This is a drop down list containing all the TNS aliases in it. If you have properly created a TNS entry in
your TNSNAMES.ORA file then you must see the name of that entry in this list. Let’s open it and see whether we have the
name of our TNS entry into this list or not. And right at the bottom we have ORCLPDB which
is our TNS alias. Let’s select it. That’s all we have to do to connect with
a pluggable database using TNS alias in Oracle 18c. Let’s press this test button and see if
we have established the connection successfully or not. Connection status: Success! Click save button to save this connection
for future use. That’s how we create a connection to our
pluggable database in Oracle Database 18c. Once you have successfully created and saved
your connection next you just have to connect with your database using it. Doing that will be pretty simple. Let me show you. But for that we first need to close this new
connection windows. Now
In the connection tab you can see all your connections which you have created and saved. For example we just created this [email protected] connection
right. To connect with your database using this connection
you simply have to right click on it and choose the first option which is connect. That’s all you have to do. Right now we are connected to our oracle database
18c using the HR user. So, in this tutorial we learnt the two methods
of connecting with pluggable database using SQL Developer. Hope you enjoyed watching and learnt something
new. Please comment and let me know how you like
this video. Also, don’t forget to like and share it
on your social media. Most importantly, subscribe to the channel
if you haven’t already. So, that’s the tutorial on how to connect
with your pluggable database using SQL Developer in Oracle Database 18c. Thanks for watching this is Manish from RebellionRider.com.

Danny Hutson

9 thoughts on “How to connect with Pluggable Database using SQL Developer by Manish Sharma

  1. Hello sir i love ur videos which is based on sql and plsql really thank you sir.
    Sir will u pls make a video tutorial on oracle forms and report 10g i really need this.
    Thank you sir /

  2. Wizard. Understandable language and words. In addition to what I have been looking for, I learned new words in English. Thank. I will recommend to those in need.

  3. I wanna thank you for getting me access to the HR tables of the 18c database. I needed them so bad to train at home for a few certifications and dozens of vids and forum post didn't give me the right solution. Your 4 vids did. So BIG hug from germany.

  4. SIr,in my sqldeveloper utility it is not connecting to the orclpdb.Testing connection is in a infinite status.May i have a solution for this??

Leave a Reply

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