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
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
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA