Posts Tagged whoami

#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

,

5 Comments

Oracle Database whoami

Just a little script that I use in my courses to see who I am :-)

C:\Users\uhesse>sqlplus sys/oracle@10.555.99.123/PROD1.us.oracle.com as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 1 10:38:29 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL> @whoami
USER: SYS
SESSION ID: 140
CURRENT_SCHEMA: SYS
INSTANCE NAME: PROD1
DATABASE ROLE: PRIMARY
OS USER: uhesse
CLIENT IP ADDRESS: 10.555.99.12
SERVER HOSTNAME: edd2r6p0
CLIENT HOSTNAME: de-ORACLE\UHESSE-DE

PL/SQL procedure successfully completed.

That’s nice, isn’t it? whoami.sql looks like this:

SQL> host type 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('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;
/

I use it especially in Data Guard courses where I connect to the Read-Only opened Standby, sometimes switch the current_schema and demo Transparent Application Failover. It is especially not necessary to grant users SELECT_CATALOG_ROLE in order to enable them to read v$instance so that they can spot their session got failed over to another instance, for example:

SQL> grant create session to fritz identified by fritz;

Grant succeeded.

SQL> connect fritz/fritz@10.555.99.123/PROD1.us.oracle.com
Connected.
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select * from v$instance;
select * from v$instance
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> @whoami
USER: FRITZ
SESSION ID: 140
CURRENT_SCHEMA: FRITZ
INSTANCE NAME: PROD1
DATABASE ROLE: PRIMARY
OS USER: uhesse
CLIENT IP ADDRESS: 10.555.99.12
SERVER HOSTNAME: edd2r6p0
CLIENT HOSTNAME: de-ORACLE\UHESSE-DE

PL/SQL procedure successfully completed.

I masked the IP addresses that have been used for the EZconnect and that have been displayed by whoami.sql. Hope you find it useful :-)

12 Comments

Follow

Get every new post delivered to your Inbox.

Join 2,644 other followers

%d bloggers like this: