Connect to a pluggable database in SQL*Plus Oracle Database Tips by Donald BurlesonNovember 25, 2015

Question: How to I use SQL*Plus with Oracle12c and beyond to connnect to a pluggable database? I need to understand the SQL*Plus connect string for a pdb.

Answer: The trick to connecting to a pluggable database (pdb) is to know the TNS service name from the tnsnames.ora ile. In this example, here is the entry for a pluggable database:

The tnsnames entry for the pluggable database is located at $ORACLE_HOME/$ORACLE_SID/network/admin/tnsnames.ora file:

pdborcl1 =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = linux-server1.com)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = pdborcl1)
      )
  )

You also need to define your environmental variables to connect to the database.

Once the environment is defined you can connect to the pluggable databaase using the TNS service_name:

$ sqlplus /nolog

SQL> connect hr@pdborcl1

Once you have connected to the container database, you can switch the login to a pluggable database inside SQL*Plus:

SQL> show con_name;

CON_NAME
------------------------
CDB$ROOT

SQL> alter session set container = pdborcl1;

Session Altered;

SQL> show con_name;

CON_NAME
------------------------
PDBORCL1