Beiträge getaggt mit whoami

#Exasol Database whoami

Veröffentlicht von Uwe Hesse in TOI am September 19, 2018

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).

Exasol, whoami

Hinterlasse einen Kommentar

#Oracle Database whoami for Multitenant

Veröffentlicht von Uwe Hesse in TOI am Januar 8, 2014

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 🙂

12c New Features, whoami

5 Kommentare

Oracle Database whoami

Veröffentlicht von Uwe Hesse in TOI am März 1, 2012

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 🙂

whoami

14 Kommentare