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.
Excellent sir add me one request add backup section oracle interview question
still, why CDB and PDB? you fail to tell the reason and show the before and after Oracle 12c situation