If you’ve worked with an Oracle database in the last few years, you may have heard
of the terms container database or pluggable database, or gotten some strange errors when
trying to connect or create a user.
So, in this video, you’ll learn what this all means, how you can work with it, and how
to avoid any strange errors you might be getting.
Welcome to the Database Star YouTube channel - the place for developers looking to improve
their database and SQL skills.
Back in Oracle 12c, which was a few years ago now, the architecture of the Oracle database
was changed.
The concepts of a “container” database and a “pluggable” database were introduced.
This concept has remained in every version of the Oracle database since 12c, so if you’re
running any version since then, this is how the database is set up.
What is a container database and what is a pluggable database?
Let’s take a look.
A container database is a way to group multiple databases together to share some common features.
Inside the container database, by default, are two databases.
One is called the root, and this is called CDB$ROOT.
This contains some Oracle metadata and information about common users.
The other is called the seed, and is called PDB$SEED.
This is kind of a template that can be used to create other databases called “pluggable
databases”.
What is a pluggable database?
A pluggable database is a collection of objects, data, and schemas.
It acts like a “regular” database, and it’s what you can connect to and work with
from your IDE.
It’s called a pluggable database because it can be plugged into different containers
and they can be generated from the seed database.
I won’t go into that in this video, but that’s why I think it’s called a pluggable
database.
Let’s see an example of a pluggable database.
If you’re using Oracle Express Edition, then the databases will look something like
this.
We have the root container database, which is called CDB Root, on the left here.
We have the seed database called PDB Seed.
It also comes with a pluggable database, or PDB, called XEPDB1.
Now, the pluggable database is like a “regular” database that you can work with.
You may have also seen this XEPDB1 value when you’ve tried to connect to the database
or browsed some documentation.
Why is this good to know?
Well, the XEPDB1 database is most likely the only database you’ll ever need to know about.
You’re probably thinking, this seems a little too complicated for a database.
I just want to connect and write SQL.
You’re right.
It can seem complicated.
But there are a few benefits to this design.
They may not apply to you if you’re just working with Oracle Express and have a single
application, but they are helpful in other cases.
The first benefit is better use of resources.
The different databases can work together to use resources more efficiently, compared
to multiple databases or virtual machines.
The second benefit is that it’s easy to move data and code in a pluggable database
from one container to another.
This may not be something you ever do, but it’s easier when you do it.
Finally, it’s easier for administrators to manage and monitor, including applying
patches and backups.
So, how do you actually work with this database now?
How can you connect?
I’ll show you how to do this in SQL Developer.
Here’s a new connection screen in SQL Developer.
I’ve given it a name to make it clear it’s connecting to the PDB or pluggable database.
That’s only for the purposes of this demo.
You can call it whatever you like.
For the username, I’ve entered System.
I’ve got separate videos on how to create users, so once you log in as System you can
create more users.
I’ve also entered the password for the System account, and checked the Save Password checkbox.
The hostname and port can remain as default, but if you’re connecting to a database at
work then your details may be different.
I’ve got the Oracle database running on my computer, and I’ve set my host to this
IP address here.
Now, here’s the important part.
When connecting to an Oracle database, and you want to connect to the pluggable database,
or just work on the “regular” database to create tables and run queries, you’ll
need to change the SID and Service Name radio button to Service Name.
It defaults to SID, so you need to change it to Service Name.
Then, in the service name field, enter the name of the pluggable database to connect
to.
If you’re running Oracle Express, then you’ll enter a value of XEPDB1, as that’s the name
of the pluggable database that comes with Oracle Express.
If you’re running a Docker image, like I am, then you’ll use a different value.
So, to repeat, the important part is to change SID to Service Name, and enter the name of
the database here.
Once you’ve done that, click Test, and the test should be successful.
Connecting to an Oracle database is the hardest part of getting started with Oracle, in my
experience.
If you have any issues here, check out the comments section below as I’m sure many
others will have issues with it.
Once you’ve tested it, click Save, and the connection is saved.
You can then connect to the database and work with it just like a regular database.
We just saw how to connect to a pluggable database.
What if you want to connect to a container database?
You can do that in SQL Developer or any other editor.
As a developer, you won’t do this very often I don’t think, but I’ll show you how.
Here’s a connection screen for connecting to the container database.
It’s similar to the pluggable database connection.
You specify the username of system and the password for that account.
The hostname and port would also be the same.
For the container database connection, you’ll also need to change the SID and Service Name
radio button to Service Name.
The value of the Service Name will depend on what database product you’re using.
For example, if you’re using a Docker container, it might be something like ORCLCDB.localdomain.
In Oracle Express, the Service Name for the container database is XE.
Now, this is where a lot of the confusion comes from.
You may have seen connection details or tutorials that advise you to connect to Oracle Express
using the service name of XE, which makes sense.
However, this will connect you to the container database.
It looks the same as a pluggable database, but then you’ll start creating tables or
users and start getting errors.
Let’s see this in action and how to fix it.
So, we’ve filled out the connection details.
Click Test, and it should be successful.
Click Save to save the connection.
I’ll now connect to the container database.
SQL Developer will now look like this.
We can run a query to check what database we have connected to.
We can use the SYS_CONTEXT function like this.
We run this query, which will show us the connected database name.
The value we see is CDB$ROOT, because we have connected to the container database.
Let’s imagine that we have connected to this database and we have assumed we are on
the pluggable database.
Or we don’t know about this container and pluggable database concept.
The first thing we may want to do is create a user.
Let’s create a user here.
This will create a new user called john, with the password of mypassword.
When we run this query, we get an error.
It says “invalid common user or role name”.
The message and the cause don’t really make sense in this situation.
Our username does only contain ASCII characters.
So what’s the issue?
If we want to create a new user, we need to create it on our pluggable database.
We’re on the container database.
If you get this error, you can either disconnect from this database and connect to the pluggable
database.
Or you can run a command to change your session to use the pluggable database.
Let’s try that now.
The command to run is Alter Session Set container equals XEPDB1.
This will change your container, or connection, to the database you specify.
So, change the value of the container here to the name of your pluggable database, which
we covered earlier in the video.
Run the command, which should be successful.
Now, you can run that same Create User command.
It should say that the new user is created.
So that’s how you can avoid any issues when trying to perform actions on a container database
when they should be done on the pluggable database.
To check the database you are connected to, you can use the same SYS Context command as
before.
It should now say XEPDB1, or the name of your pluggable database.
So I hope this video has helped you understand what container databases and pluggable databases
are, and why the recent versions of Oracle are a little different to connect to.
If you want to learn more about this concept, check out my guide to Oracle containers and
pluggable databases in the description.
If you liked this video, consider subscribing to my channel.
Thanks for watching.