V$Containers/cdb_pdbs in Oracle Database

Back to: Oracle DBA Tutorials

V$Containers/cdb_pdbs in Oracle Database with Examples

In this article, I am going to discuss V$Containers/cdb_pdbs in Oracle Database with Examples. Please read our previous article where we discussed V$DATABASE/ V$INSTANCE in Oracle.

V$Containers/cdb_pdbs in Oracle

In this article, we will learn about two data dictionaries v$containers and cdb_pdbs. Let us first discuss v$containers. This displays information about PDBs and the root associated with the current instance. Let us practically run and test these data dictionary views.

Note: Open the Linux machine and start the database and open the pluggable database. After finishing the start-up of the database, open SQL developer and connect to the database as sys as sysdba. Once the connection is successful let’s check the database using the command “show con_name”

We are in the CDB$ROOT database. Let us run a query to check the list of the both container and pluggable databases present.

Query: select con_id,name,open_mode from V$Containers;

We can see the output containing three databases. The Con_id containing 1 belongs to CDB$ROOT which is READ WRITE i.e which is accessible to all the users. The con_id containing 2 belongs to PDB$SEED which is READ ONLY. This PDB$SEED is used to create pluggable databases. This is not for all users. The last row containing con_id as 3 belongs to JAYAPDB pluggable database which is READ WRITE and accessible to all the users. So, the V$Containers show the root database and all the pluggable databases. Now let’s try the same query in the pluggable database. Let’s move from the CDB$ROOT database to the pluggable database.

Command: alter session set container=JAYAPDB;

Let us try to run the same query to check the list of container databases.

Query: select con_id,name,open_mode from v$containers;

We can only see con_id 3 and this is a pluggable database because this JAYAPDB is not a container database and this does not have any pluggable databases in it. Let us move back to the container database and we will look into the data dictionary cdb_pdbs.

Let us run a query using the data dictionary cdb_pdbs. This is another data dictionary that displays the information about the pluggable databases using the container database view.

Query: select pdb_id,pdb_name,status from cdb_pdbs;

From the output, we can see two pluggable databases with the pdb_id 3 and 2. The status of these databases is normal. The status indicates the phase of the databases. There are a few phases of the status. Let us explain each of them.

1. NEW: The PDB has never been opened since the PDB was created.
2. NORMAL: The PDB database is ready to use.
3. UNPLUGGED: The PDB is unplugged from the container. The only action during this phase is to drop the pluggable database.
4. RELOCATING: The PDB database is in the process of relocating from one container database to another container database.
5. RELOCATED: The PDB has been relocated to a different CDB.

Now, let’s check the same query in the pluggable database and see the output.

We are in the container database. Let us run the same query to check the list of pdbs.

Query: select pdb_id,pdb_name,status from cdb_pdbs;

We can see only one pluggable database because there are no other pluggable databases created inside the JAYAPDB database. If we change the data dictionary from cdb_pdbs to dba_pdbs we will get the same output.

In this article, we have learned about a list of pluggable databases using two different data dictionaries but displaying similar information.

In the next article, I am going to discuss V$datafile in Oracle Database. Here, in this article, I try to explain V$Containers/cdb_pdbs in Oracle Database and I hope you enjoy this V$Containers/cdb_pdbs in the Oracle article.