Howto get current session container in Oracle 12c

This post is also available in: Português

Oracle 12c is now available and you have the ability to keep change your session current container.

If your using SQLPlus, it's easy to check using "show con_name":

[oracle@syslogfubddsne001 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Feb 19 11:42:19 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> ALTER SESSION SET CONTAINER = PDB$SEED;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB$SEED
SQL>

Although, if you are using another client, the best way is to check trough "SYS_CONTEXT" function, as "show con_name" may not work:

Connected to Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 
Connected as SYS

SQL> select SYS_CONTEXT('USERENV', 'CON_NAME') NAME FROM DUAL;

NAME
--------------------------------------------------------------------------------
CDB$ROOT

SQL> ALTER SESSION SET CONTAINER = PDB$SEED;

Session altered

SQL> select SYS_CONTEXT('USERENV', 'CON_NAME') NAME FROM DUAL;

NAME
--------------------------------------------------------------------------------
PDB$SEED

SQL>

Note that you can also use "SYSCONTEXT('USERENV', 'CON_ID')" if your target is the container ID!

+33