Trace Analyzer "Directory alias does not exist in DBA_DIRECTORIES"

My previous post (Trace for dummies) remind me to post additional topic for those who find tkprof result too complicated. The solution is Trace Analyzer (AKA "trca"). Trace Analyzer is original Oracle product and can be downloaded from MetaLink note 224270.1.

Trca is not just "another replacement" for tkprof but big enhancement! Here is briefly why:

1. Result is saved in directory where sqlplus was started. This eliminate needs to connect to database server side what is for many developer not possible! (tkprof is server only based tool)
2. Output is full featured html with links and easy navigation through file. (tkprof is text only)
3. trca outputs bind variables values (tkprof doesn't at all)
4. Separates user recursive and internal recursive calls and provides more detailed wait event information
5. Gives optimizer statistics for indexes and tables (tkprof doesn't at all)
6. Provides information about the hottest blocks (tkprof doesn't at all)

On net there are plenty of documents that clearly describe installation (and readme.txt file is OK also) but nowhere to find solution for problem that arise after successful installation on Windows platforms only (AFAIK).

Problem reproduction

As said, regardless successful instalation, when you first time start trca in a way:

SQL> @trcanlzr.sql xe_ora_356.trc

end of output looks like:

Value passed to trcanlzr.sql:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TRACE_FILENAME: xe_ora_356.trc

...analyzing xe_ora_356.trc


Trace Analyzer executed successfully.
There are no fatal errors in trcanlzr_error.log file.
Review file trcanlzr_87242.log for parsing messages and totals.

...copying reports into local SQL*Plus client directory


...trcanlzr 43161 reports were copied from server to local SQL*Plus directory


...review file trcanlzr_87242.log for parsing messages and totals.

SQL> 

All looks OK. But when you look in directory (where html should be generated) you see:

C:\oracle\product\11g\trca\run>dir
 Volume in drive C has no label.
 Volume Serial Number is 40A5-D38E

 Directory of C:\oracle\product\11g\trca\run

21.01.2010  21:26    </dir>         .
21.01.2010  21:26    </dir>         ..
06.09.2008  09:57             5.683 trcanlzr.sql
06.09.2008  09:57             4.876 trcanlzrnc.sql
20.01.2010  23:20                56 trcanlzr_87242.html
20.01.2010  23:20                56 trcanlzr_87242.log
20.01.2010  23:20                56 trcanlzr_87242.txt
20.01.2010  23:20             8.297 trcanlzr_error.log
02.07.2008  11:28             1.967 trcapurge.sql
               7 File(s)         20.991 bytes
               2 Dir(s)  29.721.235.456 bytes free

C:\oracle\product\11g\trca\run>

In all files with 56 bytes there is same content:

*** Directory alias  does not exist in DBA_DIRECTORIES

And there is no html generated content! Something is wrong...

Analyze trace analyzer

Mine approach was to look first in dba_directories:

SQL> select DIRECTORY_NAME, DIRECTORY_PATH 
  2    from dba_directories
  3   where DIRECTORY_NAME like 'TRC%';

DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ --------------------------------------------------
TRCA$INPUT                     C:\oracle\product\diag\rdbms\xe\xe\trace
TRCA$OUTPUT                    C:\oracle\product\diag\rdbms\xe\xe\trace

SQL> 

Because default directory (input and output) for trca is user_dump_dest, on on 11g it looks like:

SQL> show parameter user_dump_dest  

NAME            TYPE     VALUE  
--------------- -------- ----------------------------------------
user_dump_dest  string   C:\oracle\product\diag\rdbms\xe\xe\trace
SQL>

And this is exactly what trace directories point to. Grants are also OK so all looks OK also!

Now it's time to see what trca is performing. Analyzing trcanlzr.sql and resulting output in trcanlzr_error.log I found one interesting part. For source code in trcanlzr.sql:

SELECT column_value FROM TABLE(trcanlzr.trca$g.directories);

result in trcanlzr_error.log looks like:

Directories
-------------------------
()
()
user_dump_dest

When you look in source (trace analyzer packages are not wrapped!), you see:

FUNCTION directories
RETURN varchar2_table PIPELINED
IS
BEGIN /* directories */
  PIPE ROW (RPAD(SUBSTR(g_input_dir||'('||get_object_status(g_input_dir, 'DIRECTORY')||')', 1, 22), 24)||get_directory_path(g_input_dir));
  PIPE ROW (RPAD(SUBSTR(g_output_dir||'('||get_object_status(g_output_dir, 'DIRECTORY')||')', 1, 22), 24)||get_directory_path(g_output_dir));
  PIPE ROW (RPAD('user_dump_dest', 24)||g_udump);
  RETURN;
END directories;

Obviously our first two rows (which are empty) come as a result for g_input_dir=TRCA$INPUT and g_output_dir=TRCA$OUTPUT.

Analyzing get_directory_path and get_object_status functions, very soon I realize that they operate with upper values ... and this is the core of the problem! Do not forget that our path values from dba_directories are in lower case (as they should be!).

Resolution

Run again as priviledged user tacdiri.sql and tacdiro.sql scripts from installation dir and put the whole path in upper case (in our case "C:\ORACLE\PRODUCT\DIAG\RDBMS\XE\XE\TRACE").

When you check dba_directories again you must see all values in upper case:

SQL> select DIRECTORY_NAME, DIRECTORY_PATH 
  2    from dba_directories
  3   where DIRECTORY_NAME like 'TRC%';

DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ --------------------------------------------------
TRCA$INPUT                     C:\ORACLE\PRODUCT\DIAG\RDBMS\XE\XE\TRACE
TRCA$OUTPUT                    C:\ORACLE\PRODUCT\DIAG\RDBMS\XE\XE\TRACE

SQL> 

Rerun trcanlzr.sql and result is finally there!

P.S.
For some slower connections to server (or bigger trace files) do not panic if you do not get result on your PC immediatelly-just wait a while. They'll come.

Cheers!