Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: view current sql

RE: view current sql

From: Mohammad Rafiq <rafiq9857_at_hotmail.com>
Date: Wed, 27 Mar 2002 09:41:10 -0800
Message-ID: <F001.0043504A.20020327094110@fatcity.com>


Alternately following script can also be used to get sql text. JUst provide SPID.... To get text:
select piece, sql_text
from v$sqltext
where (address,hash_value) in

      (select sql_address,sql_hash_value
       from v$session
       where paddr = (select addr
                      from v$process
                      where spid = '&1'))
order by piece
/

To get SPID: (Run it first)
set linesize 120
select substr(vs.username,1,10)username,

       vs.osuser,
       vs.sid,
       vs.serial#,
       vs.LOGON_TIME,
       substr(vs.machine,1,15)machine,
       vs.process,
       vp.spid,
       vs.last_call_et

from v$session vs, v$process vp
where vs.paddr = vp.addr
and vs.username is not null
and vs.status = 'ACTIVE'
/

Regards
Rafiq

Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Wed, 27 Mar 2002 03:28:34 -0800

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
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).

MOHAMMAD RAFIQ



Join the world’s largest e-mail service with MSN Hotmail. http://www.hotmail.com

--

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

Author: Mohammad Rafiq
  INET: rafiq9857_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). Received on Wed Mar 27 2002 - 11:41:10 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US