What’s up internet?
I am Manish from rebellionrider.com and I am back once again with another Oracle database
tutorial. Since I have done my last video on how to unlock HR user in oracle database
12c it seems like you people are having some problem in making a HR connection using SQL
Developer. So I have decided to do a quick video tutorial on how to connect Oracle Database
12c with SQL Developer. As this video is in the continuation of my
last tutorial thus I would request you to watch the previous tutorial first for a better
understanding of the topic. You can find the link in the description box.
Having said that let’s move ahead and start the tutorial.
As I told you guys in my previous tutorial that in Oracle Database 12c we have the concept
of Container database and pluggable database. Every container database has one Root Container
which contains system metadata to manage all the pluggable databases that belong to this
root container. When we try to connect to the database either
using SID or without specifying the TNS service name then by default Oracle 12c connects us
to this root container. However since our HR user or any other sample user is placed
inside the PDB container rather than the root container therefore in order to make a successful
HR user or any sample user connection in SQL developer we have connect to the PDB container
instead of root container. And to connect with the pluggable database
container we have to use the TNS Service. If you remember in the previous tutorial we
edited the TNSNAME.ORA file and added the TNS service entry by the name of PDBORCL.
Thus to connect with HR user we will use this PDBORCL service name while making the new
connection in SQL Developer. Let’s jump ahead and make a new connection
in SQL Developer using HR user. Before making connection make sure your pluggable
database which contains HR user must be open to perform Read and Write operations.
Sqlplus / as sysdba SELECT name, open_mode FROM v$pdbs WHERE name=
‘PDBORCL’; As you can see right now my pluggable database
is mounted so first I have to open it. ALTER PLUGGABLE DATABASE pdborcl open;
Ok everything is set, Now let’s jump to the SQL Developer and make a new connection.
To make a new connection first you need this connection panel. If you don’t have it then
you can get it from the view menu. Now click this green plus button. This will open “New
Connection” Window. Here in connection name field you can give whatever connection name
you want. I will name my connection as HR-PDB12c, Next is the user name which is obviously HR
as we are making an HR connection. In the password field you have to give the valid
password of your HR user. In my case the password is HR. Next check this Save Password Box and
if you want you can designate color to your connection here.
Next is ROLE as its HR user so let this role be default. Now there are two ways to make
an HR connection in SQL Developer. First let this connection type be Basic and
enter hostname and port number accordingly. In my case my Hostname is Localhost and I
am using default port 1521. Next field is SID if we will enter our SID
in field and try to connect then we will get an error. Let me show you
Click test. See I got this error this is because when we use SID to make a connection the Oracle
database 12c tries to connect us with the Root container and as I said HR user is not
in root container, besides root container does not even contain any user data.
Thus to make a HR connection we have to connect to the pluggable database which has our USER
for that we will use the TNS SERVICE which we created by updating the TNSNAME.ORA file
in previous tutorial by the name of PDBORCL. So click and select Service name box and enter
the name of your service. Click test. Connection is successful.
Next and relatively easy way is by using TNS connection type. Choose TNS in connection
type and from the Network alias drop down list choose the name of pluggable database
service/TNS service which is PDBORCL in our case. Now Test as you can see Connection is
successful. Hit save and connect. And here we are.
Let’s check the container name. SHOW con_name;
As you can see right now we are in the pluggable database container PDBORCL.
Next I will make another connection just to show you the difference between the connection
using TNS service and that using SID. This time I will use SYS user and SID ORCL to make
the connection. Again hit green plus button. Now on new connection
window I will name my connection as SYS-CDB12c, and username will be SYS and next will be
the valid password. Check save password box. Connection type let it be on BASIC and in
the role choose SYSDBA as the user is sys which is a highly privileged user.
Next you have to enter your hostname and port number, enter them accordingly. For valid
hostname and port number you can check your listener.ora file. In my case I will let them
be on default. Next unlike the last time, this time I will
use my SID which is ORCL instead of service name. Though you can use your service name
also but that will again connect your sys user to your pluggable database. Let’s enter
the SID Check the connection hit Test, as you can
see connection is success now hit save and then connect. And here we are. Let me quickly
show you the user. As you can see the user is sys now let’s check the container name.
SHOW con_name. As you can see unlike the last time, this
time we are connected to the root container. Always remember only common user such as sys
or system can connect to the root container as well as pluggable database container but
a normal user can only connect with the pluggable container.
With this I will wind up today’s tutorial. Hope you enjoyed watching and learnt something.
If you like this tutorial please hit the thumbs up button. You can also help me in reaching
out to more people by sharing my video with friends on your social networking. And more
Importantly Don’t forget to hit that big read button and subscribe to my channel.
Will see you soon with another tutorial till then take care this is Manish from RebellionRider.com