Lesson 5	Creating trace files for individual session
Objective	Create Oracle trace files to diagnose problems.

Creating trace files for Individual Session

While the Oracle background processes will always write to a trace file when appropriate, you can also create trace files on behalf of server processes. You may choose to do this if you are getting an unexpected result from a SQL session, or any time you want to examine every individual action that takes place during a session so that you can diagnose problems.
A relation is in third normal form when:

There are no transitive dependencies between attributes.
All non-key attributes are fully functionally dependent on the entire conatenated key.

Enabling tracing for any session

To do this, make sure that the initialization parameter SQL_TRACE is set to TRUE. This parameter is located in the init.ora file.
Regardless of the current value of SQL_TRACE, each session can enable or disable trace logging on behalf of the associated server process by using the SQL command ALTER SESSION with the SET SQL_TRACE parameter.
The following statement enables writing to a trace file for an Oracle session:

ALTER SESSION SET SQL_TRACE TRUE;

Tracing with the dbms_support package

There are other ways to turn on tracing. Sometimes, Oracle support may direct you to create a trace file using the dbms_support package. This package is supplied by Oracle support. To activate these and other traces, you must first know the SID and the serial number of the task for which you want the trace. Click the View Code button to see the SQL script you can run to get this information. It will show the SID and serial numbers for all current Oracle sessions. Enter the following at the SQL prompt: Sessions.sql

rem session.sql - displays all connected sessions
set echo off;
set termout on;
set linesize 80;
set pagesize 60;
set newpage 0;
ttitle "dbname Database|UNIX/Oracle Sessions";

set heading off;
select 'Sessions on database '||substr(name,1,8) from v$database;
set heading on;
select
       substr(a.spid,1,9) pid,
       substr(b.sid,1,5) sid,
       substr(b.serial#,1,5) ser#,
       substr(b.machine,1,6) box,
       substr(b.username,1,10) username,
--       b.server,
       substr(b.osuser,1,8) os_user,
       substr(b.program,1,30) program
from v$session b, v$process a
 where
b.paddr = a.addr
and type='USER'
order by spid;
ttitle off;
set heading off;
select 'To kill, enter SQLPLUS>  ALTER SYSTEM KILL SESSION',
''''||'SID, SER#'||''''||';' from dual;
spool off;

View Code button to view an example output from running this script.

SQL>@session
dbname Database
                              UNIX/Oracle Sessions
PID       SID   SER#  BOX    USERNAME   OS_USER  PROGRAM
--------- ----- ----- ------ ---------- -------- ------------------------------
113032    18    29694 diana1 OPS$ORACLE oracle   sqlplus@diana1 (TNS V1-V2)
113242    13    29464 diana1 OPS$JONES  oracle   sqlplus@diana1 (TNS V1-V2)123448    21    27967
24116     19    35574
45296     16    138   diana1 JOEDBA     kantoed  sqlplus@diana1 (TNS V1-V2)
83260     9     3            QDBA

Now that you have isolated the target session, you can get its SID and serial number. To start tracing the target session simply call:

DBMS_SUPPORT.START_TRACE_IN_SESSION( SID , SERIAL# );

This PL/SQL procedure will ask the target session to start writing trace output. The trace may NOT start immediately since there are certain points in the Oracle code where a session checks to see if it has been asked to do something (like writing trace output). The target session only starts tracing once it has seen the request.
To stop tracing the session call:

DBMS_SUPPORT.STOP_TRACE_IN_SESSION( SID , NULL );

Normally, you would be asked to send these traces to Oracle technical support. Now let's look at more ways in which the DBA has to set traces.