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

About these ads

  1. #1 by Kamran Agayev on March 3, 2012 - 08:43

    Hi Uwe and thanks for sharing such a nice script :)

  2. #2 by Uwe Hesse on March 3, 2012 - 12:18

    Thank YOU, my friend, for the nice feedback :-)

  3. #3 by saruamit4 on March 5, 2012 - 22:56

    Nice script. Thanks Uwe to share.

  4. #4 by Uwe Hesse on March 7, 2012 - 13:54

    You’re welcome :-)

  5. #5 by Ronny Fauth on March 19, 2012 - 14:52

    Really nice little script, but in addition i would use “ISDBA” and “LANGUAGE”. But thanks for this great idea.

    (sorry for the comment on the other post)

  6. #6 by Uwe Hesse on March 19, 2012 - 15:05

    Never mind, I will delete that one. Thanks for the nice feedback and the suggestion! I needed to pick some of the (in my opinion) most important properties of the session in order to keep it clear.

  7. #7 by levin karuoya on June 1, 2013 - 12:01

    Thanks Uwe

  8. #8 by Arun on June 4, 2013 - 05:01

    Thanks Uwe and your humanity

  9. #9 by Mark Heckler on June 6, 2013 - 22:09

    Another incredibly useful script. Thanks for creating and sharing this, Uwe!

  10. #10 by Uwe Hesse on June 13, 2013 - 10:04

    Thank YOU, Levin Karuoya, Arun and Mark for your kind words :-)

  11. #11 by Seth Williams on September 23, 2013 - 06:52

    Excellent Script. Thank you for sharing

  1. #Oracle Database whoami for Multitenant | The Oracle Instructor

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,430 other followers

%d bloggers like this: