Re: tracing the sql that an Oracle process is executing?

A quick snippet from one of my scripts is as follows:

   SET ECHO OFF
   SET VERIFY OFF
   column machine format a15
   set pagesize 40
   set linesize 100

   prompt CURRENT ACTIVE SESSIONS

   SELECT sid,serial#,username,program,machine FROM v$session;

   ACCEPT sid PROMPT 'Enter SID: '
   ACCEPT serial PROMPT 'Enter SERIAL#: '    ACCEPT action PROMPT 'Enter TRUE or FALSE: '

   EXEC sys.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(&sid,&serial,&action);

   prompt Trace &action for &sid,&serial

This script shows you information from the current sessions. Use this information to identify the process you want to trace. You'll need to know the SID and SERIAL# of that session. A call to DBMS_SYSTEM starts a trace in that session if you set it to TRUE, FALSE turns the trace off. The resulting trace file can be found in your USER_DUMP_DEST directory.

HTH,
Brian

Holger Marzen wrote:
>
> Oracle 8.1.7 on Solaris 7:
>
> We run an application with many local Oracle connections. 2 long living
> Oracle processes using lots of CPU. The application developers ask for
> help, if I could trace "something".
>
> With db2 there is a trace facility that can attach to a running db and
> write the sql commands that are executed. So I could help a db2
> deveoloper showing him that his application did the same sql again and
> again.
>
> Is there a similar way to attach to a special Oracle process and show
> the sql that this process is executing? That would help them a lot.

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"