Problem solve Get help with specific problems with your technologies, process and projects.

Enabling the sql_trace parameter

I have a database server with Oracle9i on a Solaris machine. I want to enable the sql_trace parameter in init.ora...

initialization file. I write it as

sql_trace = true 
in the init.ora file. But when I use this command
show parameter sql_trace
at the SQL prompt, it displays that sql_trace is false. I have also tried to use the command like
alter session set sql_trace = true 
but still it is display it false. How should I initialize this parameter? Actually when I issue any SQL command it is generated in the .trc file in the udump directory, but with query plan it is not there. Can I enable this parameter through the DBA admin tool? I have tried from there also, but when I restarted the database, the same problem occurs.

The SQL Trace facility is not used much anymore and I recommend it only if you need a detailed dump, i.e. when the SQL returns invalid data.

You do a detailed dump using the TKPROF utility and setting sql_trace=true is a prerequisite, but there are many other steps and you will also need special scripts to analyze the dump!

Dave Moore's book Oracle Utilities has complete instructions, and I highly recommend it.

Anyway, here are some of the set-up directions for TKPROF (from Dave's Oracle Utilities book):

Step 1: Check the Environment

Before tracing can be enabled, the environment must first be configured by performing the following steps:

  • Enable Timed Statistics – This parameter enables the collection of certain vital statistics such as CPU execution time, wait events, and elapsed times. The command to enable timed statistics is:
  • Check the User Dump Destination Directory – The trace files generated by Oracle can be numerous and large. These files are placed by Oracle in the user_dump_dest directory as specified in the init.ora.
    SQL> select value
    from v$parameter
    where name = 'user_dump_dest';
    Delete unwanted trace files before starting a new trace to free up the disk space.

Step 2: Turn Tracing On

The next step in the process is to enable tracing. Tracing can be defined at the session level:


A DBA may enable tracing for another user's session by using the following statement:
The sid (Session ID) and serial# can be obtained from the v$session view. This package is owned by the SYS user and therefore the executor must be SYS or be granted the EXECUTE privilege by SYS user.

This was last published in July 2004

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.