Re: How to find SQL statement in SQL area

2001-10-23 Thread Marin Dimitrov 

How to find SQL statement in SQL area
try this:

select  t.sql_text,
t.piece,
a.hash_value
fromv$sqltext t,
v$sqlarea a,
v$session s
where s.sql_address=t.address
and s.sql_address=a.address
order by a.address, t.piece


hth,

Marin


"...what you brought from your past, is of no use in your present. When
you must choose a new path, do not bring old experiences with you.
Those who strike out afresh, but who attempt to retain a little of the
old life, end up torn apart by their own memories. "


- Original Message -
To: Multiple recipients of list ORACLE-L
Sent: Tuesday, October 23, 2001 14:40


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: Marin Dimitrov
  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).

RE: How to find SQL statement in SQL area

2001-10-23 Thread Mark Leith 

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 > 6
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: [EMAIL PROTECTED]
===
   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: [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).