How do I connect to a PDB? 

This is a common question among new DBAs, and a valid one indeed!
We are all used to connect to a database with the “/ as sysdba” option, and this is not really working for a PDB. Or is it now? Let’s find out!

Did you know that a PDB is exposed to us as a service, as a service name. When you create a new PDB, and you open it, a service with the same name as the PDB is also created. This service name will register with the listener, and it will show in the listings of lsnrctl status, or lsnrctl service. You will use this service name of the PDB to create aliases in the tnsnames.ora file, and connect to the PDB.

Let’s see the ways we can connect to a PDB.

1) The most common way of connecting to a PDB, if you are on a database server, is to connect first to the CDB, and from there to the PDB. This way you do not need to remember passwords for the admin accounts. Let’s say you have the following container database: PRODCDB, which has one pluggable database: PDB1. To connect to PDB1, follow these steps:

set your environment, the ORACLE_SID to PRODCDB

sqlplus / as sysdba

alter session set container=PDB1;

2) Another way of connecting to the PDB, is using the EZCONNECT, and the service name of the PDB. This method can be used not only on the db server, but also on a client. You will need to provide a password for the connection. Assuming the listener is listening on port 1521, below would be the connect string:

sqlplus system/password@//dbserver_hostname:listener_port/PDB_service_name

sqlplus system/password@//proddb1:1521/PDB1
sqlplus sys/password@//proddb1:1521/PDB1 as sysdba

3) You can setup an alias in the tnsnames.ora, using the PDB service name. Using the alias to connect, you need to provide a password for the connection. Assuming you have a tns entry for PDB1, as PDB1 and PDB1_DB, your connect string would look like this:

sqlplus system/password@PDB1
sqlplus system/password@PDB1_DB

4) A new way of connecting, starting with 19c (possibly working in 18c as well), would be the use of an environment variable: ORACLE_PDB_SID. Set this environment to the PDB name, and then you could connect / as sysdba, without providing a password (on the database server).

export ORACLE_PDB_SID=PDB1
sqlplus / as sysdba

Of course, attention must be paid, to provide a valid PDB name, otherwise you would be connecting to the root container. There are some pitfalls on using this approach, as Mike Dietrich mentions in his blog post. Before connecting this way, make sure you read the post!

Now that you know how to connect, how do you check the environment you are connected to, and ensure you are connected to the database you wanted to?

I’ve got two favorite ways, both easy to remember sqlplus commands!

To see the container you are connected to, use:

show con_name

To see the available containers, and also figure out where you are connected to, use:

show pdbs

With the above command, if you are connected to the CDB root container, this command will show you all the PDBs in the container. If you are getting multiple rows returned, you know you are in the root container. If you are connected to the PDB, then this command will show you the PDB you are connected to. If you are getting one row returned, then you are in the PDB.

You can use sys_context to determine the container name or container ID, you are connected to.

SELECT SYS_CONTEXT('USERENV', 'CON_NAME') FROM dual;
SELECT SYS_CONTEXT('USERENV', 'CON_ID') FROM dual;

Remember, a good DBA always verifies where is connected before running any scripts or any commands! Warning! Selecting the name from v$database would return the same thing, whether you’re connected to the CDB root or PDB!

Always check where you’re connected!

Which one is your favorite way of connecting, and checking your environment? Let me know, I read all the emails!