#Oracle Database whoami for Multitenant

As an enhancement to the Oracle Database whoami for versions before 12c, this also shows the Container Name to which the session is connected:

[oracle@linuxbox ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jan 8 12:34:04 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> @whoami
USER: SYS
SESSION ID: 253
CURRENT_SCHEMA: SYS
INSTANCE NAME: cdb1
CDB NAME: cdb1
CONTAINER NAME: CDB$ROOT
DATABASE ROLE: PRIMARY
OS USER: oracle
CLIENT IP ADDRESS:
SERVER HOSTNAME: linuxbox
CLIENT HOSTNAME: linuxbox

PL/SQL procedure successfully completed.

SQL> connect system/oracle_4U@pdb1
Connected.
SQL> @whoami
USER: SYSTEM
SESSION ID: 253
CURRENT_SCHEMA: SYSTEM
INSTANCE NAME: cdb1
CDB NAME: cdb1
CONTAINER NAME: PDB1
DATABASE ROLE: PRIMARY
OS USER: oracle
CLIENT IP ADDRESS: 555.555.5.555
SERVER HOSTNAME: linuxbox
CLIENT HOSTNAME: linuxbox

PL/SQL procedure successfully completed.

The content of whoami.sql:

set serveroutput on
begin
dbms_output.put_line('USER: '||sys_context('userenv','session_user'));
dbms_output.put_line('SESSION ID: '||sys_context('userenv','sid'));
dbms_output.put_line('CURRENT_SCHEMA: '||sys_context('userenv','current_schema'));
dbms_output.put_line('INSTANCE NAME: '||sys_context('userenv','instance_name'));
dbms_output.put_line('CDB NAME: '||sys_context('userenv','cdb_name'));
dbms_output.put_line('CONTAINER NAME: '||sys_context('userenv','con_name'));
dbms_output.put_line('DATABASE ROLE: '||sys_context('userenv','database_role'));
dbms_output.put_line('OS USER: '||sys_context('userenv','os_user'));
dbms_output.put_line('CLIENT IP ADDRESS: '||sys_context('userenv','ip_address'));
dbms_output.put_line('SERVER HOSTNAME: '||sys_context('userenv','server_host'));
dbms_output.put_line('CLIENT HOSTNAME: '||sys_context('userenv','host'));
end;
/

Shortcut to get the name of the current container is:

SQL> show con_name

CON_NAME
------------------------------
PDB1

You may find that useful in a multitenant environment with many Pluggable Databases within one Container Database :-)

About these ads

,

  1. #1 by Mike on January 8, 2014 - 15:09

    Wouldn’t Database Name be better than Container Name to help separate it more from CBD/Instance Name & make it more sql server friendly?

  2. #2 by Anju on January 8, 2014 - 15:40

    A related post to show CDB/PDB name in SQL prompt

    http://oracleinaction.com/12c-display-cdbpdb-name-in-sql-prompt/

  3. #3 by Uwe Hesse on January 9, 2014 - 15:03

    Mike, feel free to pick any of the parameters in sys_context you like better: http://docs.oracle.com/cd/E16655_01/server.121/e17209/functions194.htm#SQLRF06117
    Not sure what you mean with ‘sql server friendly’

  4. #4 by Uwe Hesse on January 9, 2014 - 15:04

    Anju, thank you for providing this useful login script :-)

  5. #5 by Mike on January 9, 2014 - 15:24

    I didn’t mean changing the sys_context value but the output heading you chose (‘CONTAINER NAME: ‘||).

    SQL Server has let you attach/detach databases from an instance since at least 2000~ sounds a lot nicer than “pluggable” too :f

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

Follow

Get every new post delivered to your Inbox.

Join 2,747 other followers

%d bloggers like this: