Where am I at: CDB vs. PDB? – 12 edition

All right, how many of us use the V$INSTANCE view to verify the instance name and current status of the database after we connect?  In Oracle Database 12c, we can still find this information from V$INSTANCE.  Breath a sigh of relief, for the moment!  What if I connect to a container database and then issue an ALTER SESSION command to move into a different container, i.e. as PDB?  Will I get the name of the PDB that I move into from the V$INSTANCE view or do I need to look somewhere else?  Lets take a look.

Connect to the container database as usual and use the V$INSTANCE view to see where I’m at.

[oracle@oel6 dbhome_1]$ sqlplus / as sysdba

SQL> select instance_name, version, status, con_id from v$instance;

INSTANCE_NAME     VERSION           STATUS       CON_ID
----------------- ----------------- ------------ ----------
ora12cb                  12.1.0.1.0         OPEN          0

Lets move into a PDB.  I have quite a few PDBs created, lets just use PDB1.

SQL> alter session set container=PDB1;

Session altered.

Did I actually move containers?  How can I tell?  Instead of selecting INSTANCE_NAME from V$INSTANCE; we have two new SHOW commands that will provide us the information we are looking for.  These command are quite simple (SHOW CON_ID & SHOW CON_NAME).

SQL> show con_id

CON_ID
------------------------------
3
SQL> show con_name

CON_NAME
------------------------------
PDB1

Now that we are confident that we are in PDB1, lets take a look at the V$INSTANCE view again.  What you will notice is that V$INSTANCE provides us with information relative to the container database (CDB).  It only makes sense that the V$INSTANCE would return CDB information. So, how do we identify items related to the pluggable database (PDB) without the SHOW commands?

Lets take a look at a view that is similar to V$INSTANCE.  Enter the V$PDBS view!  

SQL> desc v$pdbs;
 Name                    Null?    Type
 ----------------------- -------- ----------------------------
 CON_ID                           NUMBER
 DBID                             NUMBER
 CON_UID                          NUMBER
 GUID                             RAW(16)
 NAME                             VARCHAR2(30)
 OPEN_MODE                        VARCHAR2(10)
 RESTRICTED                       VARCHAR2(3)
 OPEN_TIME                        TIMESTAMP(3)
 CREATE_SCN                       NUMBER
 TOTAL_SIZE                       NUMBER

This view provides similar information as V$INSTANCE does; yet is only specific to PDBs.  If we wanted to find out the container_id, name, open_mode and size of the PDB we are currently connected to, we can use this query:

SQL> select con_id, name, open_mode, total_size from v$pdbs;

CON_ID     NAME                           OPEN_MODE  TOTAL_SIZE
---------- ------------------------------ ---------- ----------
 3         PDB1                           READ WRITE 393216000

What is interesting to point out, is that since we used an ALTER SESSION statement to switch to PDB1, the V$PDBS view only lists the PDB we are currently working in.  If we want to get a complete list of PDBs within the container database, we need to go back to the root container database and run the same SQL statement.

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> select con_id, name, open_mode, total_size from v$pdbs;

CON_ID     NAME                           OPEN_MODE  TOTAL_SIZE
---------- ------------------------------ ---------- ----------
 2         PDB$SEED                       READ ONLY  283115520
 3         PDB1                           READ WRITE 393216000
 4         PDBTEST                        MOUNTED            0
 5         PDB2                           READ ONLY  393216000

I hope that everyone now understands how to find information related to PDBs from SQL*Plus.  

Enjoy!

twitter: @curtisbl294

blog: http://dbasolved.com

Please follow and like: