Where do my trace files go? V$DIAG_INFO


Where do my trace files go?V$DIAG_INFO October 19, 2015

Posted by mwidlake indevelopment,performance,SQL Developer.

Tags:performance,SQL,SQL developer


Where do oracle trace files go? I don’t know why this piece of info will not stick in my head, I seem to have to look it up 3 or 4 times a year.

If only I had an easy way to find out. There is a very easy way to find out – and that piece of info won’t stay in my head either. So this really is a blog post just for stupid, forgetful me.

V$DIAG_INFO has been available since oracle V11. All the trace files go into the Automatic Diagnostic Repository (ADR) by default.

ora122> desc v$diag_info Name Null? Type ------------------------------------------------------------------- -------- --------------- INST_ID NUMBER NAME VARCHAR2(64) VALUE VARCHAR2(512) CON_ID NUMBER

Quick sql*plus script to get it out:

-- diag_info-- quick check of the new v$diag_info view that came in with 11col inst_id form 9999 head instcol name form a25col value form a60 wrapspool diag_info.lstset lines 120select * from v$diag_infoorder by name/spool off


INST_ID NAME VALUE CON_ID-------- -------------------- ---------------------------------------------------------------- -------1 Diag Enabled TRUE 01 ADR BaseD:/APP/ORACLE 01 ADR HomeD:/APP/ORACLE/diag/rdbms/ora122/ora122 01 Diag Trace D:/APP/ORACLE/diag/rdbms/ora122/ora122/trace 01 Diag Alert D:/APP/ORACLE/diag/rdbms/ora122/ora122/alert 01 Diag Incident D:/APP/ORACLE/diag/rdbms/ora122/ora122/incident 01 Diag Cdump D:/app/oracle/diag/rdbms/ora122/ora122/cdump 01 Health Monitor D:/APP/ORACLE/diag/rdbms/ora122/ora122/hm 01 Default Trace File D:/APP/ORACLE/diag/rdbms/ora122/ora122/trace/ora122_ora_7416.trc 01 Active Problem Count 0 01 Active Incident Count 0 0

I should add some notes later about setting the trace file identifier…Ohhh, OK, I’ll do it now. To make it easier to identify your trace file, set tracefile_identifier

alter session set tracefile_identifier = 'mdw151019'--Now if I create a quick trace filealter session set [email protected]_codealter session set sql_trace=false

I now go to the Diag trace directory I identified via V$DIAG_INFO and look for my trace files. I could just look for the latest ones or do a wilcard search on my tracefile_identifier string and, pop, there we are:

19/10/2015 13:59 39,751 ora122_ora_7416_mdw151019.trc19/10/2015 13:59 426 ora122_ora_7416_mdw151019.trm

If you want a taste of the numerous ways of initiating a 10046 trace, sometimes called a SQL trace, see Tim Hall’s excellent post on his Oracle Base website:


Oh, one final nice thing. You can open trace files in SQL Developer and play with what information is shown. Maybe I should do a whole piece on that…