Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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: Raj <jkamaraj_at_gmail.com>
Date: Mon, 18 Jun 2007 00:12:33 -0700
Message-ID: <1182150753.545390.321020@q69g2000hsb.googlegroups.com>


On Jun 15, 12:49 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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
>
> > 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="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.
>
> 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- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

Thanks. I got the tracing to work with your help: Received on Mon Jun 18 2007 - 02:12:33 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US