Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Newbie:How to trace the SQL sent to the database from my application?

Re: Newbie:How to trace the SQL sent to the database from my application?

From: EdStevens <>
Date: Fri, 15 Jun 2007 12:50:06 -0700
Message-ID: <>

Comments embedded ....

On Jun 15, 2:30 pm, Raj <> wrote:
> Hello All,
> I am using Actuate Reporting tool to connect to Oracle10G using Oracle
> Native connection to retrieve the data. I need to capture all the
> SQL that is sent from Actuate to the Oracle from the Oracle side
> since there is no way to capture ALL sqls from Actuate side.
> I find capturing the SQL in SQL server is so easy by using the SQL
> server trace tool. But I could not find anything similar/simpler to do
> this.
> I tried the following: From PL- SQL

PL-SQL is a programming language. Do you mean SQL*Plus?
> alter system set user_dump_dest="c:\mytest";

No need to change it from the default (usually something like $ORACLE_BASE/admin/<sid>/udump). In fact doing so just serves to confuse matters, even though it *will* work.

> alter session set
> tracefile_identifier ="my_test";
> Ran this one ang got the sid for the user "System" since I am using
> the System account in Actuate to make the connection.

Bad practice, using the system account like that.

> SQL> select sid, serial#, username
> from v$session;

> Then executed the following procedure with System SID which is 139
> exec dbms_monitor.session_trace_enable(139);
> Then I ran some queris from Actuate by dragging and dropping the
> tables in the Actuate tool. Closed the Actuate
> To analyze the trace, I ran the following two commands
> C:\mytest> trcsess output="mytest.trc" service=""
> module="product update" action="batch insert"
> C:\.mytest> tkprof kfloss.trc
> output=mytest_trace_report SORT=(EXEELA, PRSELA, FCHELA)
> I got mytest_trace_report but that does not include any queries that I
> sent from Actuate.
> I think it included only the SQL I ran from the same PL-sQL session
> where I entered the alter session statements.
> Could some one please help?
> Thanks.
> Raj

Your "alter session set tracefile_identifier ="my_test";" was just that .. alter *session*. And which session did it alter? The one from which you issued the command. And which session were you tracing? Received on Fri Jun 15 2007 - 14:50:06 CDT

Original text of this message