More on how to display the current container in a multitenant database

My listener is listening for connections to a service called "myservice1", which runs out of the pluggable database "pdb1":

lsnrctl status
Service "pdb1" has 1 instance(s). <-- the default service for the pluggable database
  Instance "cdb", status READY, has 1 handler(s) for this service...

Service "myservice1" has 1 instance(s). <-- service defined by the DBA
  Instance "cdb", status READY, has 1 handler(s) for this service...

In the code snippet below, I am executing a script called disp_session.sql which will

connecting directly to a service served by the listener

switching schema within my session

The following will display the username, current schema, and the service my connction is connecting to:

alter session set current_schema=scott;
set lines 200
col service format a20
col container format a20
col username format a20
col "container ID" format a20
col schemaname format a20
select sys_context ('userenv','SERVICE_NAME')   service,
       sys_context ('userenv','CON_NAME')       container,
       sys_context ('userenv','CON_ID')         "container ID",
       sys_context ('userenv','CURRENT_USER')   username,
       sys_context ('userenv','CURRENT_SCHEMA') schemaname
from dual;

oracle@server1.oric.no:[cdb]# sqlplus system@myservice1 @disp_session.sql

Session altered.

SERVICE              CONTAINER            container ID         USERNAME             SCHEMANAME
-------------------- -------------------- -------------------- -------------------- --------------------
myservice1           pdb1                 4                    SYSTEM               SCOTT

Useful information when debugging client connections ;-)