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: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
===================================================
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
