Re: SQL stmt in SGA. Help!

From: Mary Travis <Mary.B.Travis_at_bridge.bellsouth.com>
Date: 1996/12/20
Message-ID: <59ccou$gqc@atglab10.atglab.bls.com>#1/1

kittu_at_Hawaii.Edu (Chakravarthy KM Nalamotu) wrote:

>Hi Oracle Users,
>Could some one let me know how to view the entire
>SQL text residing in the SGA. SQL_TEXT field in
>the V$SQLAREA view is 1000 char long. What if there
>is a SQL statement that is longer than that, let's say
>5000 char long. How would I view such statments.
>Thank you for your replies.
> Kittu.

Try this in SQL*Plus, given to me by most excellent Oracle DBA, Ping Zhang

ttitle CENTER "Current active SQL statements" skip 1 -

CENTER "=========================" skip 1

set pagesize 30
set linesize 80
set feedback off
col sid format 999
col username format A8
break on sid on username skip 1
select a.sid, a.username, b.sql_text from v$session a, v$sqltext b
where a.sql_address = b.address and a.sql_hash_value=b.hash_value and a.username is not null and a.status = 'ACTIVE' and a.user# != uid order by a.sid, b.piece
/
set feedback on
set pagesize 14

Any typos are my own and not Ping's!
Hope this helps.

Mary Travis
BellSouth Telecommunications
Mary Travis
BellSouth Telecommunications
email: Mary.B.Travis_at_bridge.bellsouth.com Received on Fri Dec 20 1996 - 00:00:00 CST