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

Comments embedded ....

On Jun 15, 2:30 pm, Raj <jkama..._at_gmail.com> 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="orcl10g.actutate.com"
> 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