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

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

HeliFromFinland

Heli's Oracle thoughts

Julian Dontcheff's Database Blog

The good DBA is one who learns from his mistakes, the best DBA is one who learns from other DBA's mistakes

Martins Blog

Trying to explain complex things in simple terms

Oracle Data Warrior

Changing the world, one data model at a time. How can I help you?

Maaz Anjum's Blog

A life yet to be lived...

PeteWhoDidNotTweet

Stuff that interests me, if not you!

The Oracle Instructor

Explain, Exemplify, Empower

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

Oracle Spin - Flimatech Blog

Sharing Our Database Experience

Follow

Get every new post delivered to your Inbox.

Join 1,589 other followers

%d bloggers like this: