DBA Tips Archive for Oracle


Tracing SQL and Client/Server Events

by Jeff Hunter, Sr. Database Administrator

Oracle provides two methods of tracing that you may find useful when debugging in Developer/2000: SQL*Net Tracing and SQL Server Side Tracing.

SQL*Net Tracing
If you are using SQL*Net as your connection from the client to the server, you perform SQL*Net tracing. This method will produce a dump file of all characters that are being to the server from the client. Ensure that you are turning the tracing on at the client and not the server. This makes for some pretty unhappy network administrators as the trace file can grow very large.

The table below shows how to turn on SQL*Net tracing between different operating systems and version of SQL*Net.

SQL*Net Version Operating System Commands
V1 Windows Edit ORACLE.INI, putting in a line that reads OSNTDBUG=0xffff for TCP/IP or OSNDBUG=0xffff for DECNET. This creates a file in your ORACLE home directory (ORAWIN for example) with the trace information.
V1 UNIX Set environment variable OSNTDBUG=0xffff for TCP/IP or OSNDBUG=0xffff for DECNET. This prints debugging information on stderror, which you could redirect into a file using the 2> redirection command.
V1 VMS Set the logical symbol OSNTDBUG=0xffff for TCP/IP or OSNDBUG=0xffff for DECNET. This prints debugging information on SYS$ERROR, which you could assign to a file.
V2 All Set two parameters in the SQLNET.ORA file, TRACE_LEVEL_CLIENT (value 42) and TRACE_DIRECTORY_CLIENT (value is the directory to which you want to create the trace file). This writes a file called SQLNET.TRC in the directory you specified.

SQL Server Side Tracing
You may also trace SQL statements and their performance statistics on the server side. The table below describes how to turn on SQL trace for the three different development tools:

Development Tool Commands
Forms Using the Statistics option select: Tools -> Options menu item -> Runtime options tab).
Reports In the Before-Parameter-Form trigger, insert a call to an SRW procedure:
Graphics In an Open Trigger, insert the call:

When you run your application with trace turned on, the server will produce an output trace file in the User Dump directory (USER_DUMP_DEST). The trace file will have an extension of .TRC while the file name will consist of a unique identifier (like a process id), such as ORA00223.TRC. Reading the statistics from the trace file requires the Oracle utility program: TKPROF (Toolkit Profiler). TKPROF formats the trace file into something human readable. Below is an example of running the TKPROF utility against the trace file ORA00223.TRC:

    C:\ORANT\RDBMS80\TRACE>tkprof80 ora00223.trc user_names.txt

    TKPROF: Release - Production on Fri Jun 5 6:57:53 1998

    (c) Copyright 1997 Oracle Corporation.  All rights reserved.
Click here to see the output file: user_names.txt.

Copyright (c) 1998-2018 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Saturday, 11-Nov-2000 00:00:00 EST
Page Count: 20569