String Theory (sort of) Getting the Trace File Name

Today’s little brain twister was “get the trace file name for the current session”.  

Sure it’s all there in V$DIAG_INFO (since 11) as in:

SQL> select value from v$diag_info where name = 'Default Trace File';

VALUE

-------------------------------------------------------------------------------------

C:\APP\RVANDYKE\diag\rdbms\hotsos\hotsos\trace\hotsos_ora_9240_HARNESS46_113853.trc

SQL>

But I want just the last bit, hotsos_ora_9240_HARNESS46_113853.trc.  So after a bit of re-learning on the sting functions of Oracle I can up with this:

SQL> select

  2  substr((

  3  replace((select value from v$diag_info where name = 'Default Trace File'),

  4  (select value from v$diag_info where name = 'Diag Trace'),'')),2) Tracefilename

  5  from dual;

TRACEFILENAME

------------------------------------

hotsos_ora_9240_HARNESS46_113853.trc

SQL>

The value from the second inner query with the 'Diag Trace' part gets just the directory path for the file. By replace that with a NULL (the '' part) I have just the name, except it leaves behind a \ or / depending on your OS at the beginning of the string like this:

SQL> select

  2  replace((select value from v$diag_info where name = 'Default Trace File'),

  3  (select value from v$diag_info where name = 'Diag Trace'),'') Tracefilename

  4  from dual;

TRACEFILENAME

--------------------------------------

\hotsos_ora_9240_HARNESS46_113853.trc

SQL>

So the outer substr will chop off that unwanted character and Ta-Da!  I have just the file name.   

Isn’t this SQL stuff the best!?