Wednesday, October 7, 2015

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!?

No comments:

Post a Comment