Beiträge getaggt mit whoami
#Exasol Database whoami

This little script displays some useful meta-information:
SQL_EXA> create schema myschema; EXA: create schema myschema; Rows affected: 0 SQL_EXA> create or replace script whoami as output('Current User: '.. tostring(exa.meta.current_user)) output('Current Schema: '.. tostring(exa.meta.current_schema)) output('Session ID: '.. tostring(exa.meta.session_id)) output('Database Version: '.. tostring(exa.meta.database_version)) output('Number of Nodes: '.. tostring(exa.meta.node_count)) / EXA:create or replace script whoami as... Rows affected: 0 SQL_EXA> col output for a40; COLUMN output ON FORMAT a40 SQL_EXA> execute script whoami with output; EXA: execute script whoami with output; OUTPUT ---------------------------------------- Current User: SYS Current Schema: MYSCHEMA Session ID: 1612024483893367379 Database Version: 6.1.0-alpha1 Number of Nodes: 1 5 rows in resultset.
All available metadata is documented here (Chapter 3. Concepts -> 3.6 UDF scripts).
#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 🙂
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 🙂