SET VERIFY OFF
SET SERVEROUTPUT ON
SET ECHO OFF
SET FEEDBACK OFF
SET PAGES 80
SET LINES 132

REM
REM || Title : show_unix_user.sql
REM ||
REM || Purpose : Lookup database details for a given Unix process id
REM ||
REM ||
REM || Release No : 1
REM ||
REM || Variables : None
REM ||
REM || Schema : system
REM ||
REM ||

SET SERVEROUTPUT ON SIZE 50000

UNDEFINE uid

ACCEPT uid prompt 'Enter Unix process id: '

DECLARE
v_sid NUMBER;
s sys.v_$session%ROWTYPE;
p sys.v_$process%ROWTYPE;
BEGIN

BEGIN
SELECT sid
INTO v_sid
FROM sys.v_$process p,
sys.v_$session s
WHERE p.addr = s.paddr
AND ( p.spid = &&uid
OR s.process = '&&uid');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('Unable to find process id &&uid !!!');
RETURN;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
RETURN;
END;

SELECT *
INTO s
FROM sys.v_$session
WHERE sid = v_sid;

SELECT *
INTO p
FROM sys.v_$process
WHERE addr = s.paddr;

DBMS_OUTPUT.put_line ('=====================================================================');
DBMS_OUTPUT.put_line ('SID/Serial : '|| s.sid || ',' || s.serial#);
DBMS_OUTPUT.put_line ('Foreground : '|| 'PID: ' || s.process || ' - ' || s.program);
DBMS_OUTPUT.put_line ('Shadow : '|| 'PID: ' || p.spid || ' - ' || p.program);
DBMS_OUTPUT.put_line ('Terminal : '|| s.terminal || '/ ' || p.terminal);
DBMS_OUTPUT.put_line ('OS User : '|| s.osuser || ' on ' || s.machine);
DBMS_OUTPUT.put_line ('Ora User : '|| s.username);
DBMS_OUTPUT.put_line ('Status Flags: '|| s.status || ' ' || s.server || ' ' || s.TYPE);
DBMS_OUTPUT.put_line ('Tran Active : '|| NVL (s.taddr, 'NONE'));
DBMS_OUTPUT.put_line ('Login Time : '|| TO_CHAR (s.logon_time, 'Dy HH24:MI:SS'));
DBMS_OUTPUT.put_line (
'Last Call : '
|| TO_CHAR (SYSDATE - (s.last_call_et / 60 / 60 / 24), 'Dy HH24:MI:SS')
|| ' - '
|| TO_CHAR (s.last_call_et / 60, '990.0')
|| ' min');
DBMS_OUTPUT.put_line ('Lock/ Latch : '|| NVL (s.lockwait, 'NONE') || '/ ' || NVL (p.latchwait, 'NONE'));
DBMS_OUTPUT.put_line ('Latch Spin : '|| NVL (p.latchspin, 'NONE'));
DBMS_OUTPUT.put_line ('Current SQL statement:');

FOR c1 IN (SELECT *
FROM sys.v_$sqltext
WHERE hash_value = s.sql_hash_value
ORDER BY piece)

LOOP
DBMS_OUTPUT.put_line (CHR (9) || c1.sql_text);
END LOOP;

DBMS_OUTPUT.put_line ('Previous SQL statement:');

FOR c1 IN (SELECT *
FROM sys.v_$sqltext
WHERE hash_value = s.prev_hash_value
ORDER BY piece)

LOOP
DBMS_OUTPUT.put_line (CHR (9) || c1.sql_text);
END LOOP;

DBMS_OUTPUT.put_line ('Session Waits:');

FOR c1 IN (SELECT *
FROM sys.v_$session_wait
WHERE sid = s.sid)
LOOP
DBMS_OUTPUT.put_line (CHR (9) || c1.state || ': ' || c1.event);
END LOOP;

DBMS_OUTPUT.put_line ('Locks:');

FOR c1 IN (SELECT DECODE (
l.TYPE,
-- Long locks
'TM', 'DML/DATA ENQ',
'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks
'BL', 'BUF HASH TBL',
'CF', 'CONTROL FILE',
'CI', 'CROSS INST F',
'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ',
'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ',
'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ',
'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR',
'IS', 'GET STATE ',
'IV', 'LIBCACHE INV',
'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ',
'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ',
'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ',
'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ',
'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ',
'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ',
'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ',
'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT',
'TT', 'TEMP TABLE ',
'UN', 'USER NAME ',
'WL', 'WRITE REDO ',
'TYPE=' || l.TYPE) type,
DECODE (l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX', 4, 'S', 5, 'RSX', 6, 'X', TO_CHAR (l.lmode)) lmode,
DECODE (l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX', 4, 'S', 5, 'RSX', 6, 'X', TO_CHAR (l.request)) lrequest,
DECODE (
l.TYPE,
'MR', o.name,
'TD', o.name,
'TM', o.name,
'RW', 'FILE#=' || SUBSTR (l.id1, 1, 3) || ' BLOCK#=' || SUBSTR (l.id1, 4, 5) || ' ROW=' || l.id2,
'TX', 'RS+SLOT#' || l.id1 || ' WRP#' || l.id2,
'WL', 'REDO LOG FILE#=' || l.id1,
'RT', 'THREAD=' || l.id1,
'TS', DECODE (l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
'ID1=' || l.id1 || ' ID2=' || l.id2) objname
FROM sys.v_$lock l,
sys.obj$ o
WHERE sid = s.sid
AND l.id1 = o.obj#(+))
LOOP
DBMS_OUTPUT.put_line (CHR (9) || c1.TYPE || ' H: ' || c1.lmode || ' R: ' || c1.lrequest || ' - ' || c1.objname);
END LOOP;

DBMS_OUTPUT.put_line ('=====================================================================');
END;
/

SPOOL show_unix_user.&&uid.lis
/
SPOOL off

UNDEFINE uid

SET SERVEROUTPUT OFF
SET FEEDBACK ON