Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle


Donald K. Burleson

Oracle Utilities Tips

The Oracle Trace Utility

Loading Trace Data into the Database

Although this step is technically optional, it is required in order to get data in a useful format that is understandable. The command to load the data into the database is:

otrccol format otrace_format_file

The contents of otrace_format_file include:



All of the parameters above have been explained except full_format. The options for full format include 0 (partial format) or 1 (full format).

Oracle creates the following named tables in the database to be used by Oracle Trace. Based on the naming scheme, it is clear that Oracle designed these tables for internal use only, to be used with Oracle tools. These tables will be created automatically if they don’t already exist in the schema of the connected user, defined by the username parameter.


Oracle provides many SQL scripts that access these tables and provide meaningful data, including wait events, logical transactions per second, sorts, and more. These scripts are located in the $ORACLE_HOME/otrace/demo directory. The otrcsyn.sql script can be executed to create meaningful synonyms for the tables above.

Oracle Corporation provides tools that access and display the data generated by Oracle Trace, including Trace Data Viewer (Figure 5.2) within OEM.

Deleting Trace Data

There are two places from which trace data needs to be deleted. The first is the generated cdf and dat files. The otrccol command is used with an argument of dcf, along with the collection name and name of the cdf file. This will not delete the .txt files produced by the otrcrep executable – those will need to be deleted manually.

otrccol dcf test_collection test_collection.cdf

The data also needs to be deleted. The otrccol command is used again, this time specifying dfd, the collection name, and connection information for the database.

otrccol dfd test_collection scott tiger ORCL92

otrccol, otrcrep, otrcref and otrcfmt are all executables of the Oracle Trace utility. Different components within Oracle Enterprise Manager (Oracle Expert, Oracle Trace Data Viewer) access and display the information generated by Oracle Trace. However, Oracle Trace has lost its usefulness with the progression of tools like bstat/estat, oradebug, Statspack and tkprof. These alternatives provide much of the same information as Oracle Trace while proving to be much easier to use.

Oracle Trace has a limited future while Trace Analyzer is just beginning.

For more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.



Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.