|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:
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;
from v$session b, v$process a
b.paddr = a.addr
order by spid;
set heading off;
select 'To kill, enter SQLPLUS> ALTER SYSTEM KILL SESSION',
''''||'SID, SER#'||''''||';' from dual;
View Code button to view an example output from running this script.
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.