Here's mine. It might be ugly, but it produces tons of info and looks kinda good when you send it to damagement...
set linesize 32767;
set pagesize 0;
set verify off;
set heading off;
prompt
accept pid prompt 'Enter PID: '
col program form a10 wrap
col process for a10
col sql_text for a80 wrap
col LOGIO for 999,999,999
col PHYSIO for 999,999,999
col HITRATIO for 999.99
col session_info heading "Session Info"
select
'Sid/Serial#: '||s.sid||'/'||s.serial#||chr(10)||
'Aud sid: '||S.audsid||chr(10)||
'DB User/OS User: '||S.Username||'/'||S.OSuser||chr(10)||
'Machine - Terminal: '||s.machine||' - '|| S.terminal||chr(10)||
'OS Process Ids: '||chr(10)||
' CLIENT - '||S.Process||chr(10)||
' SERVER - '||P.Spid||chr(10)||
'Client Program Name: '||s.program ||chr(10)||
'Server Program Name: '||p.program ||chr(10)||
'Module: '||s.module||chr(10)||
'Status/Type: '||s.status||'/'||s.type||chr(10)||
'Logical IO: '||(e.consistent_Gets + e.block_Gets) ||chr(10)||
'Physical IO: '||e.Physical_reads||chr(10)||
'HitRatio: '||round(100 * ((e.consistent_Gets + e.block_Gets - e.Physical_reads)
/ (e.consistent_Gets + e.block_Gets)),2) ||chr(10)||
'Logon Time: '||to_char(logon_time, 'mm/dd/yy hh24:mi:ss')||chr(10)||
'Seconds Since Active '||s.last_call_et ||chr(10)||
-- 'Terminal: '||sys_context('USERENV','TERMINAL')||chr(10)||
'Kill Command: '||'alter system kill session '''||s.sid||','||s.serial#||''';'||chr(10)||chr(10)||
'SQL Text:'||chr(10)||
t.sql_text
session_info
from v$process p, v$session s, v$sqlarea t, v$sess_io e
where p.addr = s.paddr
and s.sql_address = t.address
and s.sid=e.sid
and (s.process = '&&pid' or p.spid = '&&pid');
> -----Original Message-----
> From: Seema Singh [mailto:oracledbam_at_hotmail.com]
> Sent: Monday, March 11, 2002 10:28 AM
> To: Multiple recipients of list ORACLE-L
> Subject: SQL TEXT SEARCH
>
>
>
> Hi
> Does any one have sql scripts which show us sql text when we
> enter unix
> process id or oracle SID ?
> Please send if any one have.
> Thx
> -Seema
>
>
> _________________________________________________________________
> Send and receive Hotmail on your mobile device: http://mobile.msn.com
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Seema Singh
> INET: oracledbam_at_hotmail.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Glenn Travis
INET: Glenn.Travis_at_sas.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Mon Mar 11 2002 - 15:18:28 CST
