Queries to Find Information and Work with Oracle container database (CDB) and pluggable database (PDB)

This post lists out different administrative tasks you can perform in a container database (CDB) and pluggable database (PDB).

Determining Whether a Database is a CDB

You can query the CDB column in the V$DATABASE view to determine whether a database is a CDB or a non-CDB. The CDB column returns YES if the current database is a CDB or NO if the current database is a non-CDB.

SQL> SELECT CDB FROM V$DATABASE;

Sample output:

CDB
---
YES

Check container name and container id

You can check the container id and container name you are connected to:

SQL> show con_id con_name

Sample output:

CON_ID CON_NAME
------ --------------
1      CDB$ROOT

Viewing Information About the Containers in a CDB

To view this information, the query must be run by a common user whose current container is the root. When the current container is a PDB, this view only shows information about the current PDB.

COLUMN NAME FORMAT A8
SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

Sample output:

NAME     CON_ID     DBID       CON_UID    GUID
-------- ---------- ---------- ---------- --------------------------------
CDB$ROOT 1          659189539  1          C091A6F89C7572A1E0436797E40AC78D
PDB$SEED 2          4026479912 4026479912 C091AE9C00377591E0436797E40AC138
[PDB1]   3          3718888687 3718888687 C091B6B3B53E7834E0436797E40A9040
[PDB2]   4          2228741407 2228741407 C091FA64EF8F0577E0436797E40ABE9F

How to select a container to work with

If you would like to go to particular container, and run some queries, you can set the current container to new container.

alter session set container=[container_name];
SQL> alter session set container=MyPDB

Viewing Information About PDBs

The CDB_PDBS view and DBA_PDBS view provide information about the PDBs associated with a CDB, including the status of each PDB. To view this information, the query must be run by a common user whose current container is the root. When the current container is a PDB, all queries on these views return no results.

column pdb_name format a15
select pdb_id, pdb_name, status from dba_pdbs order by pdb_id;

Viewing the Open Mode of Each PDB

To view Open Mode of each PDB:

column name format a15
column restricted format a10
column open_time format a30
select name, open_mode, restricted, open_time from v$pdbs;

Sample output:

NAME            OPEN_MODE  RESTRICTED OPEN_TIME
--------------- ---------- ---------- ------------------------------
PDB$SEED        READ ONLY  NO         21-MAY-12 12.19.54.465 PM
[PDB1]          READ WRITE NO         21-MAY-12 12.34.05.078 PM
[PDB2]          MOUNTED    NO         22-MAY-12 10.37.20.534 AM

Showing the Services Associated with PDBs

To list the services associated with PDBs:

COLUMN NETWORK_NAME FORMAT A30
COLUMN PDB FORMAT A15
COLUMN CON_ID FORMAT 999
SELECT PDB, NETWORK_NAME, CON_ID FROM CDB_SERVICES WHERE PDB IS NOT NULL AND CON_ID > 2 ORDER BY PDB;

Sample output:

PDB             NETWORK_NAME                   CON_ID
--------------- ------------------------------ ------
[PDB1]          [PDB1].example.com             3
[PDB2]          [PDB2].example.com             4

How to open a single PDB

To open a single PDB:

SQL> alter pluggable database [PDB_name] open;

For example to open PDB named MyPDB:

SQL> alter pluggable database MyPDB open;

How to open multiple PDBs at once

To open multiples PDBs simultaneously:

SQL> alter pluggable database ALL open;

How to open all but one PDB

To exclude one PDB while opening all PDBs:

SQL> alter pluggable database ALL EXCEPT [PDB-you-don't-want-to-open] open;

For example, if you want to open all PDBs except MyPDB:

SQL> alter pluggable database ALL EXCEPT MyPDB open;

How to close PDB

To close a PDB:

SQL> alter pluggable database [PDB_Name] close;

For example:

SQL> alter pluggable database MyPDB close;

This is the simplest option. There are multiple other methods of cloning.

Initialization Parameters Modifiable in PDBs

The Query:

SQL> SELECT NAME FROM V$SYSTEM_PARAMETER WHERE ISPDB_MODIFIABLE = 'TRUE' ORDER BY NAME;

PDB History

The CDB_PDB_HISTORY view shows the history of the PDBs in a CDB. It provides information about when and how each PDB was created and other information about each PDB’s history.

column db_name format a10
column con_id format 999
column pdb_name format a15
column operation format a16
column op_timestamp format a10
column cloned_from_pdb_name format a15

select db_name, con_id, pdb_name, operation, op_timestamp, cloned_from_pdb_name from cdb_pdb_history
where con_id > 2 order by con_id;

Sample output:

DB_NAME    CON_ID PDB_NAME        OPERATION        OP_TIMESTA CLONED_FROM_PDB
---------- ------ --------------- ---------------- ---------- ---------------
NEWCDB     3      [PDB1]          CREATE           10-APR-12  PDB$SEED
NEWCDB     4      [PDB2]          CREATE           17-APR-12  PDB$SEED
NEWCDB     5      TESTPDB         CLONE            30-APR-12  [PDB2]

When the current container is a PDB, the CDB_PDB_HISTORY view shows the history of the current PDB only. A local user whose current container is a PDB can query the DBA_PDB_HISTORY view and exclude the CON_ID column from the query to view the history of the current PDB.