How to display the current container in an Oracle multitenant database

You can display the current container your session is currently connected to using three different methods.

First, connect to either the container database or one of the pluggable databases running out of your container database. In this example, I chose to connect to the CDB.


1. Display the current container using the "show con_name" or "show con_id" in sqlplus

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT --> We are connectted to the ROOT container.

Switch to a different container, and try again:

SQL> alter session set container=vpdb01;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
VPDB01 --> We are now connected to the container named VPDB01.


SQL> show con_id

CON_ID
------------------------------
6 --> The current container id is 6.

2. Display the current container using the SYS_CONTEXT function

In the example below I have thrown in a couple of other useful parameters, along with the
"con_name" and the "con_id" parameters:

col "container id" format a20
col "container name" format a20
col "container id" format 99
col "Current schema" format a20
col SID format a10
set lines 200

select SYS_CONTEXT('userenv','con_name') "container name",
        SYS_CONTEXT('userenv','con_id') "container id",
        SYS_CONTEXT('userenv','CURRENT_SCHEMA') "Current schema",
        SYS_CONTEXT('userenv','SID') "SID"
FROM DUAL;

container name       container id         Current schema       SID
-------------------- -------------------- -------------------- --------
VPDB01               6                    SYSTEM               63

From the output above, we see that we are connected to the container name VPDB01 with con_id 6.

Switch to the root container again, and verify that we are now connected to the ROOT container:

SQL> alter session set container=CDB$ROOT;

Session altered.

Rerun the SYS_CONTEXT statement:

  
select SYS_CONTEXT('userenv','con_name') "container name",
        SYS_CONTEXT('userenv','con_id') "container id",
        SYS_CONTEXT('userenv','CURRENT_SCHEMA') "Current schema",
        SYS_CONTEXT('userenv','SID') "SID"
FROM DUAL;

container name       container id         Current schema       SID
-------------------- -------------------- -------------------- --------
CDB$ROOT             1                    SYSTEM               63

3. Display the current container using V$CONTAINERS view

This final method only makes sense if you are connected to a non-root container.

SQL> alter session set container=vpdb01;

Session altered.

The result:

SQL> select CON_ID,DBID,name,OPEN_MODE
  2  from v$containers order by con_id;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         6 2049231443 VPDB01                         READ WRITE

If you are connected to the root container, and when querying the v$container, the resulting output will list all containers in your cdb:

SQL> select CON_ID,DBID,name,OPEN_MODE
  2  from v$containers order by con_id;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         1 2294367729 CDB$ROOT                       READ WRITE
         2 3254699093 PDB$SEED                       READ ONLY
         6 2049231443 VPDB01                         READ WRITE

3 rows selected.

I have also written a short blog post about the same topic here, but with a slightly different angle.