Back to: Oracle DBA Tutorials

Connection to Pluggable Database in Oracle

In this article, I am going to discuss the Connection to Pluggable Databases in Oracle with Examples. Please read our previous article where we discussed Creating Pluggable Database from SEED in Oracle with Examples.

Connection to Pluggable Database in Oracle

Until now we learned how to create a pluggable database. We have created a pluggable database named JAYAPDB1 and admin user pdb1admin in the pluggable database. Now, we will go ahead and learn how to connect to this pluggable database.

Listener Status in Oracle:

Let us go ahead and check the listener status of the database. Open the Linux machine and open a terminal and run the below command.

Command: lsnrctl status.

We can see the new entry named jayapdb1.com which indicates the newly created pluggable database. The .com is the domain name. So, we have a new service name jayapdb1.com and we can use this service name to connect to the database. Let’s go ahead and try to connect to the database. Open a terminal and connect to the JAYADB database as sysdba.

Once connected to the database we checked the container name. Let’s go ahead and check the list of pluggable databases.

Query: select name,open_mode from v$pdbs;

We can see there are 3 pluggable databases. We have PDB$SEED, JAYAPDB, JAYAPDB1. Now, let’s go ahead and connect to the new pluggable database JAYAPDB1.

Command: alter session set container=JAYAPDB1;

Let’s go ahead and check the services associated with the new pluggable database JAYAPDB1.

We can see the service name is jayapdb1. Now let’s check the domain name associated with the pluggable database.

We can see the db_domain is com. So, we learned how to query the service names. There are two methods to query service names one is using the listener status and the other is using the query select name from v$services;

Connect to the Pluggable Database using Easy Connect in Oracle:

We have already seen the easy connect method. Now let’s try that easy connect method with the new pluggable database JAYAPDB1.

Command: connect pdb1admin/goodluck@oracletest.com:1521/jayapdb1.com

We can see it’s connected. Now let’s try to connect using the SQL developer and see if the connection succeeds.

So, we have given the username as pdb1amdin and the password for the username pdb1admin and the hostname is oracletest.com, the port number is 1521 and the service name is given as jayapdb1.com and the connection status is a success. So, we learned to connect using easy and connect using the SQL developer. Let’s try to connect using the tnsnames method.

So, now we are going to open the tnsnames.ora file that we have saved previously to connect to the JAYADB database. You can check this document for more info. Connecting to Oracle using tnsnames.ora – Dot Net Tutorials

Open the tnsnames.ora and create a new entry for the JAYAPDB1. Once the tnsnames.ora file is open copy any of the current entries and paste them into the file and change the values accordingly Change the service name to jayapdb1.com and change the database name to jayapdb1 and change the connection name to jayapdb1. Save and close the file.

So, the tnsnames.ora file is added with the new entry let’s try to check with tns connection type.

So, we have given the username as pdb1amdin and password for the username pdb1admin and the hostname is oracletest.com, the port number is 1521 and the connection type is TNS and we can see JAYAPDB1 database. Let’s try to connect.

So, now the connection using the tnsnames.ora is also succeeded. Let’s try to connect from the virtual machine using the client style and see if works but before that, we have to make entries to tnsnames.ora file in the virtual machine. Go to the path mentioned below where we can see tnsnames.ora and the listener.ora file.

Open the tnsnames.ora file and make a new entry to the JAYAPDB1 database. Copy any of the previous entries and paste them down below and add changes to the entry. Change the connection name to JAYAPDB1 and change the service name and save the file.

Now, that we made an entry to the tnsnames.ora file. Let’s go ahead and try to connect to the database in the terminal using client style.

Command: connect pdb1admin/goodluck@jayapdb1

We can see it’s connected. So, we have tried connecting to the new pluggable database using multiple methods. In the next article, I am going to discuss the Creating Pluggable Database using DBCA in Oracle with Examples. Here, in this article, I try to explain the Connection to Pluggable Databases in Oracle with Examples and I hope you enjoy this Connection to Pluggable Databases article.