Trace File & Tkproof

Trace File:

when ever an internal error is detected by a process in oracle it dumps the information about the error into a trace file.

Alter session set sql_trace=TRUE

You can also add an identifier to the trace file name for later identification:

ALTER SESSION SET sql_trace = true;

ALTER SESSION SET tracefile_identifier = mysqltrace;

Stop session trace
To stop SQL tracing for the current session, execute:

ALTER SESSION SET sql_trace = false;

SQL Trace and Tkproof

The SQL Trace facility is very basic debugging diagnostic tool that can help you monitor as well as tune applications running against the Oracle Apps Forms.

The SQL trace files produced in raw form. This can be translated by the tkprof (transient kernel profiler) utility into a more human readable form.

Navigation to enable SQL Trace:

Help > Diagnostics >Trace > Trace with Binds and Waits

(Set trace with binds and waits, using help menu. Using the “binds” option, you will get to see value of the variables in SQL.)


System will give you location where trace file will be created. It always created at database tier.

Trace File Generation on Table or View

Setp.1 : Connect to your DB using Putty.

Step.2 : Set the Session trace to TRUE to enable the trace.


ALTER SESSION SET SQL_TRACE = TRUE
/

Step.3 : This procedure enables session-level SQL trace for the invoking session. Invoking this procedure results in SQL tracing of every SQL statement issued by the session.

BEGIN
    DBMS_SESSION.session_trace_enable (waits          => TRUE,
                                       binds          => FALSE,
                                       plan_stat      => 'all_executions'
                                      );
 END;

Step.4 : Query the table or view on which you want trace file.

SELECT * FROM VIEW_NAME_OR_TABLE_NAME;
/

Step.5 : Query to get the trace file name and trace status.

SELECT s.sql_trace, s.sql_trace_waits, s.sql_trace_binds,
            traceid, tracefile
      FROM v$session s JOIN v$process p ON (p.addr = s.paddr)
     WHERE audsid = USERENV ('SESSIONID')
     /

Step.6 : Disabling the Trace.


ALTER SESSION SET SQL_TRACE = FALSE