Oracle Database 12c Tutorial 3:Connect HR/SYS user with SQL Developer in Oracle12c Using TNS Service

Oracle Database 12c Tutorial 3:Connect HR/SYS user with SQL Developer in Oracle12c Using TNS Service


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

Danny Hutson

57 thoughts on “Oracle Database 12c Tutorial 3:Connect HR/SYS user with SQL Developer in Oracle12c Using TNS Service

  1. How do you get back to the password management window after you have completed installation. I need to unlock a user and give it a password

  2. Can anyone tell how to resolve below issue?
    when i type show con_name in SQL developer i face below error:

    SP2-0158: unknown SHOW option "con_name"

  3. +Manish Sharma, Hi. I'm getting SP2-0158: unknown SHOW option "con_name" error in SQL Developer. Do you know why? Many thanks.

  4. Thanks Manish Sharma…
    Ur tutorial has made our life much easier 🙂
    Great work (y). Please continue to post such useful videos 🙂

  5. how can i unlock the hr username and password? while connecting i m getting error: IO error the network adapter couldn't establish connection. Please advise on this

  6. I have used user name as : hr
    and password: hr
    plus follow the same steps like u go through, i saw some other videos but no one has explained how to get username n password to connect. Please let me know asap. Thanks!!

  7. Truly a wonderful video series, Manish. I'm taking Oracle classes at a university in a new hybrid format … Oracle University access, once a month all day class, and independent study. Most of us have been stuck on really basic things. The particular issue around getting access to the sample schemas you describe in your videos has been immensely helpful.

  8. I forgot my password that i've given on oracle installation.Pls help me to retrieve my password and make it to work.

  9. The reason SHOW con_name; works in this video is because Manish is using a newer version of SQL Developer, while the SQL Developer bundled with the Oracle 12c installation is an older version that does not have the command featured in the video. I still haven't figured out how to upgrade the bundled SQL Developer without messing up the rest of the installation.

  10. Hi Manish.. Your video is awesome. I tried the same, but am Unable to connect to my sqldeveloper, while connecting "HR", I have given Pluggable Db Container, which is "PDB1" in the service column.. and also I have put the IP address in hosts file (both – Linux & windows). Still I am facing same Error – "failure – Test failed I/O Error – network adapter could not establish connection"… Kindly help pls..

    Regards
    Prashanth K

  11. thank you for your great effort could you please show us how to connect SYS
    and HR account by sqldeveloper in orcale 12c release2 in windows
    platform

  12. After connecting to hr in unable to connect to sys , can you please help. I followed the steps from ye previous videos n I can unlock hr schema but having trouble to login to sys..

  13. Hallo Manish , I followed your steps and I could not find the file Sampleschema.xml file in sample folder you have . Where can I find this file or download it . Thank you….

  14. hey listen .. I created my own database. I want to create tables and everything in it … but it is saying limited privileges … please help me out of this. what should I do?

  15. Manish, upon executing sqlplus/ as sysdba. I am getting error:ORA-01031: insufficient privileges. what should i do?

  16. I had to assign an environment variable TNS_ADMIN = <path of directory containing tnsnames.ora> and then restart SQL Developer in order to get the list of Network Aliases to show up in the drop-down box.

  17. Dear manish bhai.I am hopeless please help me.I have installed oracle 12c successfully.When i open sql plus it is showing to enter username and password.I have tried everything.but failed.what to do now

  18. HI Unable to open my pluggable database, after following below steps;

    SQL> grant dba to system container =all;

    Grant succeeded.

    SQL> commit;

    Commit complete.

    SQL> alter session set container=pdborcl;

    ERROR:

    ORA-65024: Pluggable database PDBORCL is not open.

    SQL> alter pluggable database pdborcl open;

    alter pluggable database pdborcl open

    *

    ERROR at line 1:

    ORA-01031: insufficient privileges

    Kindly help.

  19. How to connect to remote oracle database over windows lan network what software is needed?is it necessary to work by windows server ?

  20. Hi Manish, I'm well aware you made these videos some years back. I watch with much appreciation how you try to help people to get over technical issues regarding Oracle database. On the hind side however, I notice sadly that you hardly respond to the questions of your audience across the world who follow your tutorials or instructions. Many have got genuine issues (error problems) but you simply ignore them. Please endeavour to clear their doubts and concerns in the comment box and not only thanking those who had everything done successfully. This way, I bet your subscription would grow far beyond what it is now. I've subscribed to your channel though.

Leave a Reply

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