Graphic User Interface (GUI) have always been a huge help for developers, especially the ones which are freely available and packed with heap of features. SQL Developer is one such graphic user interface. It is designed and developed by Oracle Corp. itself and is freely available for you. In this tutorial we will learn how to connect with Pluggable Database using 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. Click Here to check that tutorial.
Similar to SQL*Plus CLI, with SQL Developer also, there are two ways of connecting with a Pluggable Database in Oracle Database 18c.
- The basic way and
- Using TNS alias.
Let’s check each of these.
For the demonstration I will be using HR sample user to establish a connection with the database but if you want you can use any sample schema/user. It could be a sample schema or a user defined schema. Steps will remain the same.
Also, in order to learn either of these processes you will need SQL Developer Installed on your machine. I have done a tutorial on how to Install SQL Developer on Windows which you can watch here.
Start the process
Before we start the process of connecting with a pluggable database we need to get our desired GUI that is SQL Developer, up and running.
Step 1: Launch your SQL Developer
Yep, first thing which you have to do is to start your SQL developer. How silly of me to point out the obvious.
Step 2: New Connection Windows.
Once you have started your SQL Developer next you have to create a New Connection and you can do so by opening a new connection windows. You can open a new connection window by clicking the big green button in the connection tab.
Info: If you can’t see the connection tab on your screen then, go to the View menu and click connections.
Now that you have your SQL Developer up and running and have your new connection windows open, you are all set to start the process of connecting with a pluggable database in Oracle Database 18c.
The Common settings
Before you start configuring, you need to do some basic settings which will remain the same for both the ways (The basic way and Using TNS alias) to connect with a pluggable database in Oracle Database 18c. These settings are.
Connection Name: In this field you have to give a name to your connection. As it is a user defined field thus you can give whatever name you want to your connection.
For the demonstration I will name the connection HR@18c
Username: In this field you have to write the username (name of the schema) through which you want to connect with your database.
As For this demonstration I am using the HR schema thus in my case the username will be HR.
Password: In this field you have to specify the password of your schema with which you are connecting to your database.
As I am trying to connect with my database using HR schema thus I will specify the password for HR schema here.
Save Password: If you don’t want to enter the password every time you connect with your database then check this checkbox.
Once you are done configuring the fundamental settings next you have to configure the connection for connecting with your pluggable database.
The Basic Way to Connect with Pluggable Database
In this method of connecting with a pluggable database we have to manually configure each of the settings given in the Oracle Section of the New Connection Window. There are total six settings which we have to do here, let’s see what these are.
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 method of connecting with a pluggable database which is “The Basic Way” therefore we select “Basic” as our connection type.
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 roles assigned to it thus we will let the role stay set on “Default”.
Hostname: In this field you have to specify the hostname of the machine onto which your Oracle Database is installed.
For the demonstration I have installed the Oracle Database 18c on my laptop and its hostname is “Localhost”.
Port Number: Along with the hostname you will also need to supply the port number where Oracle Server is listening to all its network services. The default port number for Oracle Database Server is 1521.
For this demonstration I have used the default port number.
Where to find out the hostname and port number for your Oracle Database 18c?
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 listener.ora file is located at
%DB_Home%\network\admin\listener.ora
SID: SID is a site identifier. It is a unique name for your database instance. In multi-tenant architecture you do not use SID unless you want to connect with the container database. In order to connect with a pluggable database you either use a corresponding service name or a TNS alias.
For this demonstration we will not select the SID rather we will choose the next option which is Service name.
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.
All our sample schemas are placed inside a default pluggable database whose name is “ORCLPDB” and its corresponding service name is also “orclpdb”.
If you don’t know the name of your pluggable database and its corresponding service name where all your sample schemas are placed, then I suggest you to read my Oracle Database 18c tutorial 2 here.
That’s all you have to do. This the basic way to connect with your pluggable database through a sample schema like HR in Oracle Database 18c.
That is one complicated way of connecting with a pluggable database in Oracle Database 18c. Suppose you have 20 or more users created in a pluggable database. When connecting to your database using those 20 or more users you’ll need to go through the pain of configuring all these settings individually for each of those users.
Wouldn’t it be awesome if the server could do all this hard work for us? Yes, that is possible and we can replace all these settings just with one if we choose the second option which is TNS alias.
Connect with your pluggable database using 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.
I have demonstrated the entire process of creating a TNS alias in Oracle Database 18 tutorial 2 which you can check here. You will need this TNS alias to connect with your pluggable database. Thus I suggest you to read or watch Oracle Database 18c tutorial 2 and create a TNS alias for your database now.
The common settings which we discussed earlier in this tutorial will remain the same for this demonstration. The only settings which you need to do are in the Oracle Section of the New Connection Windows, let’s take a look.
Connection Name: As this time we want to connect to a pluggable database using a TNS alias thus from this drop down list we will chose the second option which is TNS.
As soon as you choose TNS the other settings of this Oracle Section will change accordingly and you will be presented with two new settings which are
- Network alias and
- Connection Identifier
Among those two options you need to select the first one which is “Network Alias”.
Network Alias: This is a drop down list containing all TNS alias which you have created in your TNSNAMES.ORA file. You need to choose the appropriate TNS alias from this list for your connection.
For this demonstration I will chose the ORCLPDB TNS alias which I have created for the HR user.
That’s all you have to do to connect with your pluggable database. For more detail on TNS alias I suggest you to watch Oracle Database tutorial 2.
Test and Save the New Connection
Once you are done configuring your Network alias next you have to Test and Save your connection.
You can press the “test button” to check whether everything is O.K. with your settings or not. Once you are sure that you have configured all the settings correctly then press the “Save Button” and save your connection.
These are the two ways to connect with your pluggable database in Oracle Database 18c. For more details, watch Oracle Database 18c tutorial 4 here.
Hope you enjoyed this tutorial, do make sure to share the link of this blog on your social media & help others learn too. Thanks and have a great day.
