A Guide to Oracle PDB (Pluggable Database) and CDB (Container Database)

If you’ve worked with Oracle databases recently, you may have heard of the terms “pluggable database” and “container database”.

In this guide, you’ll learn:

• what these terms mean
• the benefits of this new architecture
• how to connect to a container or pluggable database
• how to switch between these databases
• how to create a container or pluggable database
• how to see information about a container or pluggable database

Let’s get into it.

 

What is a Container Database and Pluggable Database?

In Oracle 12c, a new architecture or design of the databases was introduced, called “container databases” or “multitenant architecture”.

The Oracle database can function as a “multitenant container database”, otherwise known as a CDB. This CDB can include zero or more “pluggable databases”, or PDBs.

A PDB is a collection of schemas and objects that act like a “regular” database to applications and IDEs.

If you’ve been working with Oracle for a while and this CDB and PDB structure is new to you, then the simple answer is that a PDB is like a “regular database” that you work with.

But, it’s much more than that.

 

Containers

An Oracle CDB has many containers. A container is either a PDB or the root.

Here’s a diagram to represent it.

This diagram shows that the database contains the CDB. Inside the CDB are two containers:

• Root, named CDB$ROOT. This contains Oracle metadata and common users.
• Seed PDB, named PDB$SEED. This is a template that can be used to create new PDBs. You can’t add or modify objects in this PDB.

There are no other PDBs created by default in an Oracle database, if you’re using the full version of Oracle.

PDBs can be created by you (assuming you have the right privileges).

Here’s a diagram showing the same database with a new PDB created, called PDB1.

We’ll explain how to create a PDB later in this guide.

If you’re running Oracle XE (also known as Oracle Express), you have a PDB created already, called XEPDB1.

This is what it looks like as a diagram:

We can see the root container, the seed PDB, and the default PDB (called xepdb1) that comes with the database.

 

Benefits of Multitenant Architecture

So, Oracle uses this new CDB and PDB architecture. What are the benefits of this? Can’t you just create different databases or VMs?

Well, you can, but the benefits of using this architecture are:

• Better use of resources: PDBs and CDBs use resources on the server more effectively compared to VMs (which duplicate the operating system) and separate databases (which don’t share processes)
• Easier movement of data and code: if you need to move a pluggable database from one container database to another, this is quite easy
• Easier management and monitoring: for administrators, applying patches, upgrades, monitoring the database, performing backups, and other tasks are much easier.

The non-CDB architecture (the way the databases work before 12c) is available in recent versions, but it was deprecated in Oracle 12c and desupported in Oracle 20c.

 

Connect to an Oracle CDB

You can connect to an Oracle container database (CDB) in the same way as connecting to any other database.

If you’re a developer, you may not do this very often, as many of your connections and work will be done on a PDB.

In SQL Developer this is done by specifying:

• Username: a username that exists on the database, such as SYSTEM
• Password: the password for that user
• Service Name: this will depend on your database.
  • If you’re using the Docker image: ORCLCDB.localdomain
  • If you’re using Oracle Express: XE (yes, the service name for the container database in Oracle Express is XE)

Here’s the connection screen in SQL Developer if you want to connect to Oracle Express.

 

Connect to an Oracle PDB

If you want to connect to an Oracle pluggable database (PDB), you can do that in a similar way to a CDB.

There are a couple of things to remember:

• Select Service Name instead of SID. SID is only used if you want an alternative way to connect to a container database.
• The PDB must exist in order to connect to it. If you’re using Oracle XE, or the docker image on Docker Hub, one has been created.
• The connection to the PDB is done by specifying the service, not the PDB itself. They can often have the same names but may have different names.

In SQL Developer this is done by specifying:

• Username: a username that exists, such as one you have created or SYSTEM
• Password: the password for that user
• Service Name: the name of the service that runs the PDB.
  • If you’re using the Docker image, ORCLPDB1.localdomain
  • If you’re using Oracle Express: XEPDB1.

Here’s the connection screen for connecting to a PDB in Oracle Express.

 

Switch Between Containers (CDB and PDB)

If you’ve connected to one of the containers, you can easily change your session to be connected to another container.

This means you can:

• change from a PDB to the CDB
• change from the CDB to a PDB
• change from one PDB to another PDB

This is helpful if you connect to the wrong container or want to work on a different container.

You can change containers by using the Alter Session command.

ALTER SESSION SET container=pdb1;

This will change your connection to the pdb1 database, which is a pluggable database.

If you’re on Oracle XE, your command may look like this:

ALTER SESSION SET container=xepdb1;

To change to the CDB, you specify CDB$ROOT.

ALTER SESSION SET container=CDB$ROOT;

You can then run the SQL that you want to on the container you’re connected to.

 

View Information about the CDB and PDB

Once you’ve connected, you may want to know what container you’re connected to and a bit more information about the environment.

There are a few things you can see.

 

View Services

You can see all of the services on the database, which are the names that are specified when you want to create a new connection. This is useful to get an idea of the PDBs on the database and to find the details if you want to create a new connection.

SELECT name, pdb
FROM v$services;

Results:

NAME	PDB
orclpdb1.localdomain	ORCLPDB1

In this example, we can see the name of the service (which is what is used to connect to the database on the connection screen), and the name of the PDB that is used.

 

View Current Container Name

Showing the name of the container you are connected to is very handy, as it can help you decide what commands to run next or whether you need to switch containers.

This is done with the SYS_CONTEXT function. You can use the SHOW CON_NAME command, but this only works on SQL*Plus.

To find the container name, use the parameter of CON_NAME:

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

We’ve specified the Oracle DUAL table because we don’t need data from any table here.

When we run this on the CDB, we see this:

SYS_CONTEXT(‘USERENV’,’CON_NAME’)

CDB$ROOT

When we run this on a PDB, we see this:

SYS_CONTEXT(‘USERENV’,’CON_NAME’)

ORCLPDB1

So, this can help us see the name of the container we’re running this on.

 

View Connection Information

We can expand on the use of the SYS_CONTEXT function to show the container ID and the database name.

SELECT
SYS_CONTEXT('USERENV','CON_NAME') AS con_name,
SYS_CONTEXT('USERENV','CON_ID') AS con_id,
SYS_CONTEXT('USERENV','DB_NAME') AS db_name
FROM dual;

This will show the following information when run on a CDB:

CON_NAME	CON_ID	DB_NAME
CDB$ROOT	1	ORCLCDB

When run on a PDB, here’s what you see:

CON_NAME	CON_ID	DB_NAME
ORCLPDB1	3	ORCLPDB1

The CON_ID values are predetermined.

• 0 is for the whole multitenant database
• 1 is for the root container
• 2 is for PDB$SEED
• Values from 3 onwards are used for PDBs

 

Creating a Container Database (CDB)

To create a new CDB, use the Create Database command with the suffix Enable Pluggable Database.

CREATE DATABASE … ENABLE PLUGGABLE DATABASE;

This will create a new CDB, with a root container of CDB$ROOT and a new seed PDB of PDB$SEED.

If you omit the ENABLE PLUGGABLE DATABASE, then this new database is a non-CDB, and can never be changed to contain PDBs.

 

Create a Pluggable Database (PDB)

To create a pluggable database, you need to be connected to the CDB with the container set to the root (which is the default). You must also have the Create Pluggable Database privilege.

To create a PDB:

CREATE PLUGGABLE DATABASE my_new_pdb;

This will create a new pluggable database called my_new_pdb. This will contain the full data dictionary and internal links to objects in the root container.

There are several other ways to create a PDB:

• Create a PDB from the PDB$SEED database
• Create a PDB from an existing PDB
• Create a PDB from a remote PDB
• Unplug a PDB from a CDB and plug it into a different CDB

Once the PDB is created, you can connect to it and begin working on it.

 

Oracle Views

Oracle database contains many dynamic views in the data dictionary that are used to see information about objects.

These views had prefixes, such as dba_, all_, and user_. Each of these prefixes indicates views that show different levels of information.

After Oracle 12c and the CDB/PDB functionality, the information shown in each of these types of views was different. There is also a new series of views added which have the prefix of cdb_.

Here’s how they changed:

Prefix of View	Before CDBs	After CDBs
cdb_	Did not exist	All objects in all containers (root and all PDBs)
dba_	All objects in the database	All objects in the current container (root or PDB)
all_	Objects accessible by the current user	Objects accessible by the current user in the current container (root or PDB)
user_	Objects owned by the current user	Objects owned by the current user in the current container (root or PDB)

You may not notice a difference in how you use these, but it’s good to know how they have changed.

 

Conclusion

The Oracle multitenant architecture may seem confusing if it’s new to you, but the concept of a container DB and a pluggable DB can be understood easier after you work with it for a while.

If you’re a developer and work with an Oracle database, you may not notice any difference except your connection strings are different.

There are many advantages of working with pluggable databases for administrators.

I hope this article was helpful for you to understand CDBs and PDBs.

If you have any questions or comments, leave them in the comments section below.