trca gotchas -- Directory alias does not exist in DBA_DIRECTORIES

This is the scenario:

1. You traced the sql
2. You trca-ed the trace file, which showed: Trace Analyzer executed successfully. There are no fatal errors in this log file.
3. Yet when you open the html, there is nothing in there except “Directory alias does not exist in DBA_DIRECTORIES”.

http://damir-vadas.blogspot.com/2010/01/trace-analyzer-directory-alias-does-not.html has one reason and solution, but that didn’t work for me. My oracle is installed in Windows, TRCA$INPUT and TRCA$OUTPUT are in lower case:

SQL> select DIRECTORY_NAME, DIRECTORY_PATH

  2      from dba_directories

  3      where DIRECTORY_NAME like 'TRC%';

DIRECTORY_NAME                 DIRECTORY_PATH

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

TRCA$OUTPUT                    c:\oracle\diag\rdbms\orcl\orcl\trace

TRCA$INPUT                     c:\oracle\diag\rdbms\orcl\orcl\trace

But this didn't pose any problems, so I had to search in other places.

After debugging trca packages, I found out that if the size of the trace file is too small, trcanlzr.sql would consider it is not really a trace file, instead a text file containing the list of the trace files. Remember there are 2 ways to use trcanlzr.sql:

SQL> start trcanlzr.sql largesql.trc  <== your trace file

SQL> start trcanlzr.sql control_file.txt  <== your text file

If the trace file size is small, trcanlzr.sql will think it is the second usage. The logic is in tacpkg.lis:

 

-- if file is small then it means it is a control file with trace filenames in it, else is just one trace

IF l_file_length < TO_NUMBER(trca$g.g_trace_file_min_size_bytes) THEN -- control file with multiple traces

Obviously this is a very stupid assumption. The second stupid mistake trca made is that the output “Directory alias does not exist in DBA_DIRECTORIES” bears no relationship with the real root cause!

The default value for this small size threshold is:

  

SQL> select p.value,p.default_value,p.low_value, p.high_value from TRCANLZR.TRCA$_TOOL_PARAMETER p where name='trace_file_min_size_bytes';

VALUE    DEFAULT_VALUE  LOW_VALUE HIGH_VALUE

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

9999    9999             0          99999

You might think “LOW_VALUE” is the low threshold, but according to the logic is in tacpkg.lis, “VALUE” is actually the threshold. Update “VALUE” to 1, trcanlzr.sql can now successfully generate a report:

SQL> update TRCANLZR.TRCA$_TOOL_PARAMETER  set value=1 where name='trace_file_min_size_bytes';