Using Trace Analyzer in 12.2 in the Cloud or not

NOTE: After some more testing, it may be the end of the line for Trace analyzer.  The "SQL Genealogy" section in particular appears to have statements repeated many times.  This tool hasn't been updated in a long time and I'm in contact with the folks who own the code so we'll see if it is updated or not.

It’s an old tool but still a useful one. If you used oracle’s trace analyzer tool to analyzer 10046 trace files in the past, you’ll need to adjust it a bit to get it to work in 12.2.  You can download it from My Oracle Support, Doc ID 224270.1.

First off the trace locations are off a bit; I noticed this first in 11 actually.  Simply change one of the directories used by the trace analyzer tool to the current directory.  

You need a SYSDBA connection to do this first part.

To get your current trace location, use this query:

SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';

Then do the following:

CREATE OR REPLACE DIRECTORY TRCA$INPUT1 AS '<your trace directory>';

Now connect as the trace analyzer user, this should be TRCANLZR and whatever password you set while doing the install of trace analyzer.  Now do these changes:

Set the length of the PARTITION_NAME column to 60 in these tables:

TRCA$_SEGMENTS

TRCA$_EXTENTS

TRCA$_TOOL_WAIT_SEGMENT  

TRCA$_GROUP_WAIT_SEGMENT

TRCA$_GROUP_EXEC_WAIT_SEGMENT

TRCA$_HOT_BLOCK_SEGMENT

Set the length of the SUBOBJECT_NAME column to 60 in these tables:

TRCA$_OBJECTS        

TRCA$_OBJECTS$

Example:

ALTER TABLE TRCA$_SEGMENTS MODIFY PARTITION_NAME VARCHAR2(60);

Now you should be all set to use it!