Re: How to identify SQLText of a Process/Session?

badri_at_cc.gatech.edu (badri) wrote:

> Hi:
>
> Given all the information in V$SESSION and V$PROCESS, can I
> get the SQL text of that particular process? These views only give me
> the command type (as in select or insert, but I want the entire statement).
>
> In other words, I want to be able to see the SQLText of a
> given ORACLE_PID or SPID or SID/SEQ# combination.
>
> Can this be done?
>
> thanks a lot,
>
> -Badri
>
> p.s: How else can I use V$SQLarea to tie back to processes?

Here's a sql script I picked up in this very group. I think it will do what you're after with little or no modification. I use the clever name of SHOWSQL.sql.

(I apologize to the original poster - I didn't get your name to properly credit you...)

=-=-=-=-=-=-

set heading off
set echo off
set termout off
column sql_text format a55 word_wrapped
column username format a25
spool xtmpx.sql

select 'select '''||username||'('||sid||','||serial#|| ')'' username, sql_text ' ||
'from v$sqlarea where address = hextoraw( ''' || rawtohex(sql_address) || ''' );' sql_text from v$session where username is not null and rawtohex( sql_address ) <> '00'
/
spool off
set heading on
set termout on
_at_xtmpx.sql
set echo on

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- Chris Kasten
Programmer/Analyst

#include <std/disclaimer.h>
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Received on Wed Apr 03 1996 - 00:00:00 CEST