Re: Capture an application's ad-hoc SQL like MS SQL Profiler

On Dec 18, 6:14 pm, Niall Litchfield <niall.litchfi..._at_dial.pipex.com> wrote:
> stefan.karaiva..._at_gmail.com wrote:
> > I need to see ad-hoc SQL executed by an application whose source code I
> > do not have. How can I run a trace which would give me a list of all
> > SQL statements (SQL, stored procs, SQL statements inside the stored
> > procs, triggers, SQL inside triggers, etc) with bound variable values
> > where needed in the order they were executed? That is, I need the
> > Oracle equivalent of an MS SQL Profiler trace.
>
> > I have tried SQL_TRACE, DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION, and
> > TKPROF but I am thinking there might be an easier way of doing this.
> > It's been days of reading and trying to set this up. Isn't there a tool
> > free or commercial that already does that?
>
> > Also, with this DIY way, how can I limit the output of the TKPROF. I am
> > not interested in statistics at all at the moment. All I need is the
> > SQL text, the bound variable values if there are any, and the start
> > time of execution.
>
> > Thankssql_trace will do this. The output is a text file (or text files). They
> can of course grow large. The challenges though are similar to sql
> profiler files - except you get better info from them.
>
> --
> Niall Litchfield
> Oracle DBAhttp://www.orawin.info/services- Hide quoted text -- Show quoted text -

Stefan, here are a couple of aritcles that may be of use to you

How do I switch on sql trace in another session that is already running?
http://www.jlcomp.demon.co.uk/faq/alien_trace.html

Is there a way to trace a unix process id to a SID and SERIAL# ? http://www.jlcomp.demon.co.uk/faq/sid_from_proc.html

HTH -- Mark D Powell -- Received on Mon Dec 18 2006 - 19:35:01 CST