Just where is my trace file?

This is pretty cool.

There is a view you can use to see some settings from a diagnostic point of view: V$DIAG_INFO

SQL> DESC V$DIAG_INFO
 NAME                 NULL?    TYPE
 -------------------- -------- --------------
 INST_ID                       NUMBER
 NAME                          VARCHAR2(64)
 VALUE                         VARCHAR2(512)
 CON_ID                        NUMBER

It's a fairly small view with 11 rows in it (in  my 12.1.0.1 database on my laptop at least).  The name column shows the kind of information stored:

SQL> select name from v$diag_info;

NAME
----------------------------------
Diag Enabled
ADR Base
ADR Home
Diag Trace
Diag Alert
Diag Incident
Diag Cdump
Health Monitor
Default Trace File
Active Problem Count
Active Incident Count

Of interest to this post is Default Trace File.  When selecting this it shows not just the trace file name but also the entire path.  On my little laptop this is what I have:

SQL> SELECT VALUE FROM V$DIAG_INFO WHERE NAME='Default Trace File';

VALUE
--------------------------------------------------------------------
C:\APP\ORACLE\diag\rdbms\hotsos\hotsos\trace\hotsos_ora_9616.trc

And if I set TRACEFILE_IDENTIFIER that will get reflected in the name:

SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER = 'RVD_TEST';
Session altered.
SQL> SELECT VALUE FROM V$DIAG_INFO WHERE NAME='Default Trace File';

VALUE
--------------------------------------------------------------------------
C:\APP\ORACLE\diag\rdbms\hotsos\hotsos\trace\hotsos_ora_9616_RVD_TEST.trc

Nice.