Pickleball spielen

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 🙂