Help, where am I? CDB or PDB … and which DB anyway?

About problems with Oracle 12c Multitenant Database

Back in the days everything was easier, wasn’t it? Thus far you connect to your database and in doubt you send a query to v$instance or v$database to check you’re actually connected to the right place. But since 12c the world is no more that clear. Meanwhile we’ve got the Multitenant Option and by connecting to a Pluggable Database, maybe you won’t get the desired result.

But for what reason it is such a problem?

In a multitenant environment you can connect directly to your Pluggable Database, but also jump between Pluggable Databases and the Root Container back and forth. When you work with several sessions and also several windows, you can lose track sometimes. Also for scripting it can be helpful to make changes to the right PDB and not to the Root Container by mistake.

For the following example we work with a simple 12c Multitenant Database with CDB (the ROOT Container) and an attached Pluggable Database (PDB).

CDB = SJW12C01 and PDB = SJWPDB01

Our v$instance is not very helpful anymore regarding PDB, even the result is completeley correct:

CDB

sqlplus system@SJW12C01

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

INSTANCE_NAME        CON_ID VERSION
---------------- ---------- -----------------
sjw12c01                  0 12.1.0.2.0

PDB

sqlplus system@SJWPDB01

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

INSTANCE_NAME        CON_ID VERSION
---------------- ---------- -----------------
sjw12c01                  0 12.1.0.2.0 

Unfortunately the same thing with v$database:

CDB

sqlplus system@SJW12C01

SQL> select name, con_id, db_unique_name from v$database;

NAME          CON_ID DB_UNIQUE_NAME
--------- ---------- ------------------------------
SJW12C01           0 SJW12C01

PDB

sqlplus system@SJWPDB01

SQL> select name, con_id, db_unique_name from v$database;

NAME          CON_ID DB_UNIQUE_NAME
--------- ---------- ------------------------------
SJW12C01           0 SJW12C01

With v$pdbs we do have a first option to get helpful information. You will get similar results with select * from cdb_pdbs. In the example we jump per alter session set container from CDB to PDB and back:

CDB > PDB > CDB

sqlplus system@SJW12C01

SQL> select dbid, con_id, name from v$pdbs;

      DBID     CON_ID NAME
---------- ---------- ------------------------------
4043975488          2 PDB$SEED
4185832567          3 SJWPDB01

SQL> alter session set container = SJWPDB01;

Session altered.

SQL> select dbid, con_id, name from v$pdbs;

      DBID     CON_ID NAME
---------- ---------- ------------------------------
4185832567          3 SJWPDB01
 
SQL> alter session set container = CDB$ROOT;

Session altered.

SQL> select dbid, con_id, name from v$pdbs;

      DBID     CON_ID NAME
---------- ---------- ------------------------------
4043975488          2 PDB$SEED
4185832567          3 SJWPDB01

As you can see, you get informations about a PDB$SEED in the Container Database. The SEED Database is used as a draft in the multitenant environment for creating new PDB’s. In a PDB you will always get only one result in every case, your current PDB.

But there is a better way using USERENV from SYS_CONTEXT:

CDB

sqlplus system@SJW12C01

SQL> select sys_context('USERENV','CON_NAME') CON_NAME,
            sys_context('USERENV','CON_ID') CON_ID,
            sys_context('USERENV','DB_NAME') DB_NAME from DUAL;

CON_NAME   CON_ID     DB_NAME
---------- ---------- ----------
CDB$ROOT   1          SJW12C01

PDB

sqlplus system@SJWPDB01

SQL> select sys_context('USERENV','CON_NAME') CON_NAME,
            sys_context('USERENV','CON_ID') CON_ID,
            sys_context('USERENV','DB_NAME') DB_NAME from DUAL;

CON_NAME   CON_ID     DB_NAME
---------- ---------- ----------
SJWPDB01   3          SJW12C01

Pay attention to the DB_NAME. It will always return the name of the (Root) Container Database. You may considered the CON_ID. Don’t be confused – CON_ID 0 means the whole Multitenant Database and 1 is reserved for the Root Container. CON_ID 2 is always PDB$SEED. Everything else from CON_ID 3 up is a Pluggable Databases.

Having that in mind and with a bit of DECODE you get the current database name and if you are connected to a CDB or PDB:

CDB

sqlplus system@SJW12C01

SQL> select decode(sys_context('USERENV', 'CON_NAME'),'CDB$ROOT',sys_context('USERENV', 'DB_NAME'),sys_context('USERENV', 'CON_NAME')) DB_NAME, 
            decode(sys_context('USERENV','CON_ID'),1,'CDB','PDB') TYPE 
       from DUAL;

DB_NAME    TYPE
---------- ----
SJW12C01   CDB

PDB

sqlplus system@SJWPDB01

SQL> select decode(sys_context('USERENV', 'CON_NAME'),'CDB$ROOT',sys_context('USERENV', 'DB_NAME'),sys_context('USERENV', 'CON_NAME')) DB_NAME,
            decode(sys_context('USERENV','CON_ID'),1,'CDB','PDB') TYPE 
	   from DUAL;

DB_NAME    TYPE
---------- ----
SJWPDB01   PDB

Easier and a bit faster is using SHOW CON_ID, SHOW CON_NAME via SQL Plus. But it only works here and SHOW PDBS only works as SYSDBA. Pay attention to the connect / as sysdba – freshly connecting I change from PDB to CDB. Don’t be confused by the parameter db_name. Concentrate on con_id and con_name:

CDB > PDB > CDB > PDB

sqlplus system@SJW12C01

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      SJW12C01

SQL> show con_id

CON_ID
------------------------------
1

SQL> show con_name

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

SQL> alter session set container = SJWPDB01;

Session altered.

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      SJW12C01

SQL> show con_id

CON_ID
------------------------------
3

SQL> show con_name

CON_NAME
------------------------------
SJWPDB01

SQL> conn / as sysdba

Connected.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 SJWPDB01                       READ WRITE NO

SQL> show con_name

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

SQL> show con_id

CON_ID
------------------------------
1

SQL> alter session set container = SJWPDB01;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 SJWPDB01                       READ WRITE NO

Remark:

Starting Oracle 12.2 sys_context(‘USERENV’,’DB_NAME’) will show the name of the Database in CDB$ROOT and the name of the PDB inside the PDB.