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: <>
Date: Fri, 15 Jun 2007 12:49:29 -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
> alter system set user_dump_dest="c:\mytest";
> 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.

Why on earth are you using SYSTEM as a reporting user?!? The Actuate setup program should create a properly privileged user account for such activity.

> 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.

Then your assumption is incorrect that you're using SYSTEM to connect through Actuate, or you missed the other SYSTEM session which Actuate would be using. In effect you traced your current session.

> Could some one please help?

You need to stop using SYSTEM as your Actuate user, period. Then you need to look for the session your Actuate software is using and trace that. You have the mechanics of this process correct; your concept of which session to trace needs some work. I would be checking the PROGRAM column in V$SESSION and find the account using your Actuate executable.

> Thanks.
> Raj

David Fitzjarrell Received on Fri Jun 15 2007 - 14:49:29 CDT

Original text of this message