Can't find trace file for trace started by DBMS_SYSTEM.SET_SQL_TRACE_IN

Home -> Community -> Usenet -> c.d.o.server -> Can't find trace file for trace started by DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION

From: <james.barton_at_blueyonder.co.uk>
Date: 6 Oct 2006 07:38:41 -0700
Message-ID: <1160145521.577587.316940@h48g2000cwc.googlegroups.com>

Hi,

I'm trying to analyse the database activity caused by a Java method. >From what I can tell it uses only one session, so I should need only one trace file.

The Oracle instance is on a RAC system with two Linux hosts. I'm new to RAC systems, so this might be the source of my problem. When I'm logged in through a development client like PL/SQL Developer or SQL Plus I can create trace files for my own sessions. When I look in the specified user_dump_dest, there they are.

The problem I'm having is that I can't find a trace file for the Java method. I knew I couldn't start a trace wih my previous permissions, so I did this:

logged on to the host as Oracle
connected to the instance in the normal way
issued "connect / as sysdba"
granted execute on dbms_system to my username

No errors were reported, so I logged out, restarted PL/SQL developer, and tried to start a trace on a running session. This told me that "identifier 'SYS.DBMS_SYSTEM' must be declared". I tried the same through Enterprise Manager, which told me that a trace had been started but did not produce a file in user_dump_dest.

I then logged in to the instance as sys, as above, and issued

begin dbms_system.set_sql_trace_in_session(x,x, true); end; /

A few minutes later I issued it with a "false" to turn off tracing. Still no file in user_dump_dest, on either of the hosts. So I have a few questions:

how do I tell categorically if I've granted execute on dbms_system to my user (and was there anything wrong with my grant)?
can Enterprise Manager fail silently, or be wrong, when it claims that tracing is enabled for a session?
if I enable tracing on a session and nothing happens (or no new query starts) before I turn it off, shouln't I still get an "empty" trace file?
why would a trace command from SYS ever fail if trace commands form other users didn't? Where should I look for error messages?

thanks for any help or documentation references, James Received on Fri Oct 06 2006 - 09:38:41 CDT