Oracle-L: RE: view current sql

From: Mark Leith <mark_at_cool-tools.co.uk>
Date: Wed, 27 Mar 2002 03:28:34 -0800
Message-ID: <F001.00434989.20020327032834@fatcity.com>

Here's a script that I picked up from Tim Onions site the other day..

REM Script created by Tim Onions, June 1998 REM You are free to use this script (although no guarentees are made or liability accepted from the author)
REM on one condition - use it to improve the performance of your database!

@@sessions
Prompt
Prompt Enter session ID for which full SQL text is require: col STMT format a100 heading 'Statement' col UNAM format a20 heading "User|Details"
--Asterixes after username indicates current session
col RUNT format a08 word heading 'Time Since|Last Activity' set pages 0 feedback off lines 200
set verify off lines 130
set recsep off

clear breaks
break on unam skip on runt skip
set head on pages 20
Prompt

select nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')'||DECODE(Ses.AUDSID,userenv('SESSIONID'),'**','') UNAM,

       SQL.SQL_TEXT     STMT
     ,    ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'
       || ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
       || ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09'))    RUNT
  from V$SESSION SES
     , V$SQLTEXT_WITH_NEWLINES      SQL
 where SES.USERNAME is not null
   and SES.SQL_ADDRESS    = SQL.ADDRESS

and SES.SQL_HASH_VALUE = SQL.HASH_VALUE and ses.sid = &&Session_ID
order by sql.piece
/

clear columns
clear breaks
undef Session_ID
set feedback on pages 40 recsep wrap

www.timonions.com

HTH Mark

 Mark Leith             | T: +44 (0)1905 330 281
 Sales & Marketing      | F: +44 (0)870 127 5283
 Cool Tools UK Ltd      | E: mark_at_cool-tools.co.uk
===================================================
           http://www.cool-tools.co.uk
       Maximising throughput & performance

-----Original Message-----

Gilbert
Sent: 27 March 2002 10:23
To: Multiple recipients of list ORACLE-L

How can I see the current sql instruction from a specific user.
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Bernard, Gilbert
INET: Gilbert.Bernard_at_caissedesdepots.fr

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: Mark Leith
INET: mark_at_cool-tools.co.uk

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