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

From: Brian M. Biggs <bbiggs_at_cincom.com>
Date: 1996/04/03
Message-ID: <3162BD98.4AC8_at_cincom.com>#1/1

Chris Kasten wrote:
>
> 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.

Or this one...you can play with the columns selected and their widths to only display information you're interested in. Or make username or sid a parameter.

One question for anyone who might have the answer: Is there a way to get more than the first 1000 characters of the SQL statement? That's all the v$sqlarea view provides. Is there another view I can look at to get the full text of the statement?

• Brian

  ---

  SET PAGESIZE 60 SET FEEDBACK ON SET TERMOUT OFF

COLUMN sid         HEADING "SID"         FORMAT 990
COLUMN user_name   HEADING "USERNAME"    FORMAT A16 TRUNCATED
COLUMN sql_text    HEADING "SQL TEXT"    FORMAT A30
COLUMN executions  HEADING "EXECS"       FORMAT 999999
COLUMN sorts       HEADING "SORTS"       FORMAT 999
COLUMN buffer_gets HEADING "BUFFER|GETS" FORMAT 999
COLUMN disk_reads  HEADING "DISK|READS"  FORMAT 999

BREAK ON user_name SKIP PAGE

COLUMN name NEW_VALUE db_name NOPRINT
SELECT name FROM v$database
/

SET TERMOUT ON PROMPT
PROMPT Open cursor information for database &&db_name:

SELECT a.user_name || '(' || b.sid || ')' AS user_name,
       c.sql_text, c.sorts, c.executions,
       c.disk_reads, c.buffer_gets

FROM v$open_cursor a, v$session b, v$sqlarea c WHERE a.saddr = b.saddr AND a.address = c.address ORDER BY a.user_name
/

CLEAR BREAKS
CLEAR COLUMNS
SET PAGESIZE 14

-- 
Brian M. Biggs                             mailto:bbiggs_at_cincom.com
Cincom Systems, Inc.                       voice: (513) 677-7661
http://www.cincom.com/

Received on Wed Apr 03 1996 - 00:00:00 CEST