RE: How to find SQL statement in SQL area

From: Mark Leith <mark_at_cool-tools.co.uk>
Date: Tue, 23 Oct 2001 04:47:25 -0700
Message-ID: <F001.003B2053.20011023045517@fatcity.com>

Helmut,

To get the full text of the statement you will need to do a join with v$sqltext. An example:

select   s.sid
         a.address,
         a.buffer_gets,
         a.executions,
         t.piece,
         t.sql_text
   from  v$sqlarea a,
         v$sqltext t,
         v$session s
   where a.ADDRESS = t.ADDRESS
   and   a.HASH_VALUE = t.HASH_VALUE

and a.hash_value = s.sql_hash_value and a.buffer_gets > 60000
order by 1,4;

The full text of the statement will be in a number of rows (ordered by the piece column).

BTW, I saw that you are also using NORAD. To get the full text of a statement in this - go in to the SQLAREA screen (about 11 or 12 buttons from the left, that looks like a disk share icon) and simply double click on the sql that you are interested in, in the grid view. This will show you the full text of the statement, and the users that are currently executing it.

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-----
Helmut
Sent: Tuesday, October 23, 2001 12:40
To: Multiple recipients of list ORACLE-L

Hi!
How do I find the ENTIRE SQL statement that is dogging my box? select sql_text from v$sqlarea where hash_value=123456 gives me the first part of the SQL statement; but since the statement is pretty long, the sql_text column does not contain the entire statement. Is there a way to retrieve the entire statement? I also know the session ID of the application issuing the statement. This is 8.1.7 on Sun Solaris.
Thanks,
Helmut

--

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). Received on Tue Oct 23 2001 - 06:47:25 CDT