#!/bin/ksh
#
# Author: Mohit Dubey
# Visit http://www.geocities.com/md_seraphin for more goodies!
# 
# This program is distributed under the GNU Public License Version 2
# with the additional privisio that the original author's name and
# contact details must be retained as-is in any modified or copied
# versions of this program.
# 

trap "stty echo; exit" ERR 2

if [[ ${#} -ne 2 ]]
then
   printf "\n\tUsage:\n"
	printf "\t%s [<uname>@<dbname> <unix-pid>]\n" ${0##*/}
   printf "\nLogin [uname@dbname]   : "
   read ORALOG1 foo
   printf "Unix PID to investigate: "
   read UXPID foo
else
	printf "\nParsing parameters..."
   ORALOG1=${1}
   UXPID="${2}"
	printf "done.\n\n"
fi

printf "Schema password        : "
stty -echo
read ORALOG2 foo
stty echo

printf "\n\nProcessing...\n\n"
ORALOGIN=${ORALOG1%%@*}"/"${ORALOG2}"@"${ORALOG1##*@}

if $(sqlplus -s ${ORALOGIN} <<-EoTST | /usr/xpg4/bin/grep -q ORA-
      exit;
EoTST
)
then
   printf "\nInvalid Oracle credentials! Logon denied.\n\n"
else
   printf "\n"
   sqlplus -s ${ORALOGIN} <<-EoSQL | more
set pages 0 lines 132 pau off timing off trimspool on;
set serveroutput on size 100000;
set echo off feed off veri off
set escape \\
DECLARE
        s   v\$session%ROWTYPE;
        p   v\$process%ROWTYPE;
        cursor sesCur(p_id IN number) 
        is
          select sid, s.serial#
            from v\$process p, v\$session s
           where p.addr     = s.paddr
             and (p.spid    = p_id
              or s.process = to_char(p_id));
BEGIN
        dbms_output.enable(1000000);
        for sesRow in sesCur(${UXPID})
        loop
           select * into s from v\$session where sid=sesRow.sid and serial#=sesRow.serial#;
           select * into p from 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, '99990.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 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 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 v\$session_wait where sid = s.sid) loop
              dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
           end loop;

           -- dbms_output.put_line('Connect Info:');
           -- for c1 in ( select * from v\$session_connect_info where sid = s.sid) loop
           --    dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
           -- 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  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 loop;
EXCEPTION
         when others then
            dbms_output.put_line(SQLERRM);
END;
/
exit;
EoSQL
fi
printf "\n"