Re: Grabbing SQL statements from memory

From: Juergen Krauth <juergen.krauth_at_de.origin-it.com>
Date: Wed, 19 Aug 1998 14:53:10 +0200
Message-ID: <35daca35.24492843@www-proxy.hbg.de.origin-it.com>

On Wed, 19 Aug 1998 10:30:00 +0200, "Nexus Product Centre" <clarkej_at_logiica.com> wrote:

>Hi guys and gals,
>
>Can anyone help me with the following problem??
>
>I have an Oracle 7.3.2.x database and need to track some SQL statements
>that are causing a problem.
>
>I can identify the problem user's sid and serial# from v$session and can
>join to v$open_cursor to get the sql_text.
>
>Unfortunately this is defined as VARCHAR2(60) so I get a truncated SQL
>statement.
>
>How can I retrieve the full statement???
>
>TIA
>
>Jon

Hi Jon,

I hope this will help you.

set line 120
set pagesize 30000
col sql_text format a80
col serial# format 999999
col schemaname format a10

select 	schemaname,
	osuser,
	s.serial#,
	sql_text

from v$process p, v$session s, v$sqlarea sq where addr = paddr

and     sql_address = Address
and     schemaname = 'SCOTT'

bye

Juergen Krauth Received on Wed Aug 19 1998 - 07:53:10 CDT