Re: How to trace SQL calls?

Hi Andy,
  If you don't want to have to bounce you database and run a trace each time you need the info, you could run the following in SQL*PLUS (the input parameter is the process id, which I get from ps ux on UNIX).
I call this when my CPU starts getting hogged by a user process. (First I capture the hogging process id, then run this script.)

NOTE: This works on a version 7 database, not on 6.

spool &1
select sql_text
from v$sqltext
where address = (select sql_address from v$session where paddr = (select addr from v$process where spid = &1)) and hash_value = (select sql_hash_value from v$session where paddr = (select addr from v$process where spid = &1)) order by piece
/

Feel free to email if you'd like more details. -Donna Bigler
donnab_at_intermec.com

Andy Hardy (aph_at_ahardy.demon.co.uk) wrote: <snip>
: I have some long enquiries running and I'd like to see what they are up
: to!
: How do I trace these SQL calls?
: I've tried using SQL*DBA, and the session monitor almost does what I
: want but it seems to truncate the SQL call!
: Andy Hardy (Internet: aph_at_ahardy.demon.co.uk, CIS: 100015,2603)
Received on Thu Oct 19 1995 - 00:00:00 CET