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 @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 @connect mXXXXXXX@pdb1.eis Rem Enter password: ********* Rem Connected. Rem MXXXXXXX @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, user user_nm, host_name server_nm, 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) 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(30); 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 SET FEEDBACK ON