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

What's up internet? I'm Manish from rebellionrider.com and I'm 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 my previous tutorial first for the 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 meta data 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 connect us to the 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 to connect to the PDB container instead of the root container. And to connect with the pluggable database container, we have to use the DNS service. If you remember in the previous tutorial, we edited the tns name.ora file and add the tns service entry by the name of PDBL. Thus to connect with HR user we will use this PDB OCS 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 operation. Let's check as you can see right now my pluggable database is mounted. So first I have to open it. Okay, everything is set. Now let's jump to the SQL developer and make a new connection. To make a new connection, you need this connection panel. If you don't have it, you can get it from the view menu. And here it is. Okay. Now click this green plus button. This will open up a new connection window. Here in connection name field, you can give whatever connection name you want. I will name my connection as HR-PDB2LC. Next is the username 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 it's 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 your host name and port number accordingly. In my case, my host name is local host and I'm using my default port which is 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 how. 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. Beside 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 a user. For that we will use a tns service which we created by updating the tns name.ora aura file in previous tutorial by the name of PDB OCL. So click and select the 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 DNS in connection type and from the network alias drop-own list choose the name of the pluggable database service. Select TNS service which is PDB OCL in our case. Now click test. As you can see connection is successful. Hit save and connect. And here we are. Let's check the container name. As you can see, right now we are in the pluggable database container which is PDB OCL. 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 SIS user and SID OCL to make the connection. Again, hit that green plus button. Now on new connection window, I will name my connection as SIS- CDB12C and username will be SIS. And next will be the valid password. Check save password box and connection type let it be on basic and in the role choose cis dba as the user is sis which is highly privileged user. Next you have to enter your host name and port number. Enter them accordingly. For valid host name and port number you can check your listener. 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 OCL instead of the service name. Though you can use your service name also but that will again connect your SIS 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 SIS. Now let's check the container 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 CIS or system can connect to the root container as well as to the 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 learned 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 your friends on your social networking. And more importantly, don't forget to hit that big red button and subscribe to my channel. We'll see you soon with another tutorial. Till then, take care. This is Vanish from Rebellion Rider.