Skip to Main Content
  • Questions

  • enable tracing doesn't create the files in the server

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, yair.

Asked: October 11, 2011 - 5:19 am UTC

Last updated: November 29, 2012 - 6:37 am UTC

Version: 10.2.0.4

Viewed 10K+ times! This question is

You Asked

hi,
i tried to trace sessions so i ran dbms_monitor or dbms_system and got no error and it didn't create the files in udump what could be the reasons? thanks

and Tom said...

the reason could be that you were using shared server - then the dump files go into bdump - not udump. that could be one reason.

another reason could be that you
a) opened the trace file
b) erased the trace file
c) opened it again - it will not appear because you erased the file entry (the directory entry) in (b) - but the file still exists (unix works like that)

there could be many other reasons perhaps - but you don't give us very much to work with here. You don't show your work, you don't describe your setup, you don't prove to us that the file isn't there, you don't show evidence that you knew how to build the file name correctly from the v$ tables and so on.


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

problem details

yair, October 12, 2011 - 7:33 am UTC

you're right,
I should notice that i have already use the tracing utility so i'm fimiliar with it (i know what the trace file name should be).
we don't use shared server, it isn't a job that i'm tracing and when i've checked udump and bdump i didn't saw the file.

when i run dbms_monitor/dbms_session on a session (which i know that it exists) and i query v$session for that session i see that in the SQL_TRACE column the value is disabled, why?

Thanks!
Tom Kyte
October 12, 2011 - 8:18 am UTC

cut and paste from sqlplus your step by steps please.

something like this:

ops$tkyte%ORA11GR2> select status, sid, serial#, sql_trace from v$session where username = USER;

STATUS            SID    SERIAL# SQL_TRAC
---------- ---------- ---------- --------
ACTIVE             11       3283 DISABLED
INACTIVE          198       2333 DISABLED

2 rows selected.

<b>my active session is "me", the rest of this cut and paste is "me".  I'm going to activate tracing on the inactive session</b>


ops$tkyte%ORA11GR2> !ls -l /home/ora11gr2/app/ora11gr2/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_26143.trc
ls: /home/ora11gr2/app/ora11gr2/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_26143.trc: No such file or directory

<b>I figured out what its trace file name would be - file does not exist</b>

ops$tkyte%ORA11GR2> exec dbms_monitor.session_trace_enable( 198, 2333 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> !ls -l /home/ora11gr2/app/ora11gr2/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_26143.trc
ls: /home/ora11gr2/app/ora11gr2/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_26143.trc: No such file or directory

<b>file still doesn't exist, it won't until that session does something to cause it to exist</b>

ops$tkyte%ORA11GR2> select status, sid, serial#, sql_trace from v$session where username = USER;

STATUS            SID    SERIAL# SQL_TRAC
---------- ---------- ---------- --------
ACTIVE             11       3283 DISABLED
INACTIVE          198       2333 ENABLED

2 rows selected.

<b>but tracing is on and if I go into that other session at this point and issue "select * from dual", then</b>

ops$tkyte%ORA11GR2> !ls -l /home/ora11gr2/app/ora11gr2/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_26143.trc
-rw-rw---- 1 ora11gr2 ora11gr2 3645 Oct 12 09:16 /home/ora11gr2/app/ora11gr2/diag/rdbms/ora11gr2/ora11gr2/trace/ora11gr2_ora_26143.trc

<b>trace file appears</b>



so, do that for us.

Why no trace file?

Jeremy, November 22, 2012 - 5:33 am UTC

I've enabled tracing on the whole system (in a quiet-ish development database) using alter system set events '10046 trace name context forever, level 12', then a colleague has kicked off some work against the same database that we want to trace, following that I switch off tracing using
alter system set events '10046 trace name context off';

The trace on/off commands show in the alert log, but no trace files appeared in bdump or udump (this is a 10.2.0.4 database on Windows 64-bit).

Assuming that the work done actually accessed the database (it's a print service reading a table of documents) can you think of why no trace files appeared? Given that the trace was system wide I was expecting to get something from the background tasks at the very least. The trace was on for 53 seconds.

Tom Kyte
November 29, 2012 - 6:37 am UTC

as long as you hadn't done this before (turned on and then off) in this instance, it should have generated trace files, yes. Please utilize support - I have no windows databases anywhere to play with.

D'Oh

Jeremy, November 22, 2012 - 5:46 am UTC

SQL_TRACE=FALSE - that would explain it wouldn't it!


Tom Kyte
November 29, 2012 - 6:37 am UTC

not sure what you mean?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

DBMS_MONITOR

More on PL/SQL routine DBMS_MONITOR here