Rem Filename   : whoami.sql
Rem 
Rem Parameters : None
Rem
Rem Author     : 
Rem RDBMS      : Oracle V8.x, V9.x
Rem
Rem Modification history
Rem --------------------
Rem 10-May-2000  Initial creation.
Rem 14-Mar-2001  Modified script so that is also works when connected
Rem              as internal in SQLPLUS.
Rem              Added "AND username is not null" to the select from 
Rem              v$session.
Rem 03-Jul-2001  Added statements for setting the SQL prompt to the
Rem              current user and the database to which he/she is
Rem              connected.
Rem 22-Jan-2002  Adapted to 64-bit Oracle : replaced raw(4) by raw(8).
Rem 04-Mar-2003  Improved prompt, in case of unsuccessful connection.
Rem 16-Sep-2005  - Added the server name in the displayed SQL prompt.
Rem              - Added the instance name to the displayed information.
Rem 07-oct-2005  - Added "AND process is not null" to SELECT from v$session
Rem                in order to avoid "dupplicated row" errors caused by
Rem                Oracle Streams background processes when connected
Rem                as SYSDBA.
Rem              - Added some extra logic to deal with multiple sessions
Rem                session connected as SYSDBA.
Rem              - Display some basic information about the client.
Rem
Rem =========================================================================================
Rem
Rem Description  : Identifies your current session.
Rem
Rem Remark       : If the RDMS is running on a Unix platform then the server Pid is 
Rem                displayed.
Rem                If the RDMS is running on an NT4 or Windows 2000 platform then the
Rem                server thread is displayed.
Rem                If the client process is Windows hosted, then in addition to the 
Rem                client Pid, also the client thread is given as shown in the example 
Rem                below.
Rem
Rem Known issues : There is no documented way to distinguish between multiple sessions that
Rem                are connected as SYSDBA.
Rem                audsid appears to returns 0 (zero) for all session connected as SYSDBA.
Rem
Rem Sample output
Rem -------------
Rem SYS<deeisep1:PDB> @whoami
Rem You are SYS logged on to the PDB database (instance = PDB1) at the deeisep1 server.
Rem There are multiple sessions connected as SYSDBA.
Rem Your session cannot be distinguished.
Rem SYS<deeisep1:PDB> @connect mXXXXXXX@pdb1.eis
Rem Enter password: *********
Rem Connected.
Rem MXXXXXXX<deeisep1:PDB>   @whoami
Rem You are MXXXXXXX logged on to the PDB database (instance = PDB1) at the deeisep1 server.
Rem Your sid and serial are (46,2461).
Rem Your server Pid(or thread) = 7707 and your client Pid[:ThreadId] = 1180:708.
Rem Your client node = EMEA\W2SZMS2K02, OS user = szms2k and ip address = <130.177.145.71>.
Rem
Rem =========================================================================================

SET TERMOUT  OFF
SET ECHO     OFF

-- Without the following defines no prompt is displayed in case of an unsuccessfully
-- connect attempt. Reason : both variables are empty and SQL*Plus attempts to prompt 
-- for them, but since both TERMOUT and ECHO are set OFF nothing is visible to user.

DEFINE user_name = 'NotConnected'
DEFINE database_name = 'Unknown'
DEFINE server_name = 'Na'
DEFINE inst_name = 'Na'

DEFINE vsid = 'Na'
DEFINE vserial = 'Na'

COLUMN inst_db       NEW_VALUE inst_name NOPRINT
COLUMN name_db       NEW_VALUE database_name NOPRINT
COLUMN user_nm       NEW_VALUE user_name     NOPRINT
COLUMN server_nm     NEW_VALUE server_name   NOPRINT

COLUMN v_sid     NEW_VALUE vsid   NOPRINT
COLUMN v_serial     NEW_VALUE vserial   NOPRINT

SELECT name      name_db,
       substr(user,1,3)      user_nm,
       host_name server_nm,
	   --upper(host_name)||':'||upper(instance_name) inst_db
	   upper(instance_name) inst_db
  FROM v$database, v$instance;

select trim(sid) v_sid, trim(serial#) v_serial
from v$session
where AUDSID = USERENV('SESSIONID');

COLUMN name_db       CLEAR
COLUMN user_nm       CLEAR
COLUMN server_nm     CLEAR
COLUMN inst_db     CLEAR
COLUMN v_sid     CLEAR
COLUMN v_serial     CLEAR

Rem SET SQLPROMPT    '&user_name<&server_name:&database_name>'

--HOST title SQL &user_name@&inst_name (&vsid,&vserial)

HOST title &user_name@&inst_name (&vsid,&vserial)

SET TRIMSPOOL    ON
SET SERVEROUTPUT ON
SET TERMOUT      ON
SET FEEDBACK     OFF


declare
    user_naam         varchar2(30);
    own_sid           v$session.sid%type;
    own_serial        v$session.serial#%type;
    own_paddr         v$session.paddr%type;
    own_clientproc    v$session.process%type;
    client_node       v$session.machine%type;
    client_osuser     v$session.osuser%type;
    own_unix_pid      v$process.spid%type;
    my_count          integer;
    computer_naam     varchar2(100);
    database_naam     varchar2(30);
    name_instance     varchar2(30);
    ip_address        varchar2(20);

begin
    SELECT name, user, SYS_CONTEXT('USERENV','IP_ADDRESS') 
      INTO database_naam, user_naam, ip_address
      FROM v$database;

    SELECT host_name, instance_name
      INTO computer_naam, name_instance
      FROM v$instance;


    SELECT count(*) INTO my_count 
      FROM v$session
     WHERE audsid = userenv('SESSIONID') 
       AND username is not null
       AND process  is not null;

    if my_count = 1 then   
      SELECT sid, serial#, paddr, process, machine, osuser
        INTO own_sid, own_serial, own_paddr, own_clientproc, client_node, client_osuser
        FROM v$session
       WHERE audsid = userenv('SESSIONID') 
         AND username is not null
         AND process  is not null;

      SELECT spid
        INTO own_unix_pid
        FROM v$process
       WHERE addr = own_paddr;

      dbms_output.put_line( 'You are ' || user_naam || 
                            ' logged on to the ' || database_naam || 
                            ' database (instance = ' || name_instance ||
                            ') at the ' || computer_naam || ' server.' );
      dbms_output.put_line( 'Your sid and serial are (' || own_sid || ',' || own_serial || ').' );
      dbms_output.put_line( 'Your server Pid(or thread) = ' || own_unix_pid || 
                            ' and your client Pid[:ThreadId] = ' || own_clientproc || '.' );
      dbms_output.put_line( 'Your client node = ' || client_node || 
                            ', OS user = ' || client_osuser || 
                            ' and ip address = <' || ip_address || '>.' );

    else   
      dbms_output.put_line( 'You are ' || user_naam || 
                            ' logged on to the ' || database_naam || 
                            ' database (instance = ' || name_instance ||
                            ') at the ' || computer_naam || ' server.' );
      dbms_output.put_line( 'There are multiple sessions connected as SYSDBA.' ); 
      dbms_output.put_line( 'Your session cannot be distinguished.' );
    end if; 

end;
/

UNDEFINE user_name
UNDEFINE database_name
UNDEFINE server_name
UNDEFINE inst_db
UNDEFINE vsid
UNDEFINE vserial

SET FEEDBACK ON
