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
show parameter sql_trace
alter session set sql_trace = true
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:
ALTER SYSTEM SET TIMED_STATISTICS = TRUE;
- 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'; VALUE --------------------------------- C:oracle9iadminORCL92udumpDelete 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:
ALTER SESSION SET SQL_TRACE = TRUE; DBMS_SESSION.SET_SQL_TRACE(TRUE);
Dig Deeper on Oracle and SQL
Related Q&A from Don Burleson
I have created a table, Party, and in it there are two main fields, party_code and party_name, and around 2,500 records are available in it. I passed... Continue Reading
Suppose you have a table which has only two columns, namely locid (not primary) and place. In Oracle, which query should I run to get the output as ... Continue Reading
I have a query that retrieves around 10 records. I have a task to concatenate the values separated with ','. I would like to know if Oracle has any ... Continue Reading