enable trace in Oracle

How to enable trace in Oracle

1. Enable trace at instance level

Put the following line in init.ora. It will enable trace for all sessions and the background
processes

sql_trace = TRUE

to disable trace:

sql_trace = FALSE

- or -

to enable tracing without restarting database run the following command in sqlplus

SQLPLUS> ALTER SYSTEM SET trace_enabled = TRUE;

to stop trace run:

SQLPLUS> ALTER SYSTEM SET trace_enabled = FALSE;


2. Enable trace at session level

to start trace:

ALTER SESSION SET sql_trace = TRUE;

to stop trace:

ALTER SESSION SET sql_trace = FALSE;

- or - 

EXECUTE dbms_session.set_sql_trace (TRUE);
EXECUTE dbms_session.set_sql_trace (FALSE);

- or -

EXECUTE dbms_support.start_trace;
EXECUTE dbms_support.stop_trace;


3. Enable trace in another session

Find out SID and SERIAL# from v$session. For example:

SELECT * FROM v$session WHERE osuser = OSUSER;

to start trace:

EXECUTE dbms_support.start_trace_in_session (SID, SERIAL#);

to stop trace:

EXECUTE dbms_support.stop_trace_in_session (SID, SERIAL#);

- or -

EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, TRUE);
EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, FALSE);

- or -

-- Oracle 10.1
EXECUTE DBMS_SYSTEM.set_sql_trace_in_session(sid=>SID, serial#=>SERIAL, sql_trace=>TRUE);
EXECUTE DBMS_SYSTEM.set_sql_trace_in_session(sid=>SID, serial#=>SERIAL, sql_trace=>FALSE);


-- to trace session with wait events
alter session set events '10046 trace name context forever, level 8';

-- to see wait events and bind variables
alter session set events '10046 trace name context forever, level 12';

Tracing Techniques within the Oracle Applications 11i/R12

A trace file is a log of SQL run in a particular session or sesesions  focused on selects, inserts, updates, and deletes.  A trace file can be used in many circumstances including reviewing performance,  finding tables and views referenced, or finding the root of an error. 
 
How does one enable trace in the Oracle Application screens / forms?


One can enable trace through the forms by using the Help menu, choosing the daignostics menu,trace and then selecting the appropriate trace for your needs .
Most commonly if debugging an error, you should at least provide trace with binds When debugging a performance issue, you may consider using trace with binds and waits.


For example, the following is the navigation to enable trace in a form:

Goto the Oracle Applications -->Login -->Open the form where the error occurs but do not yet cause the error.
Enable SQL*Trace by choosing Help > Diagnostics > Trace > Trace with binds

A message appears indicating that the trace will be recorded
Note the file name and location of the file
Now reproduce the error.
Once the error occurs, disable trace as soon as possible.

Disable SQL*Trace by choosing Help > Diagnostics > Trace > Trace off
The same file name and location will be noted in case you need it again.
Retrieve the trace file.
How does one enable trace for a concurrent program?


A simple way to enable trace in a concurrent program is to review the concurrent program definition and select trace enabled. This will enable trace without binds for all users that run the program. 

For example, the following steps could be used. 
Goto Sysadmin > Concurrent > Program > Define 
Query the concurrent program 
Check the trace box to enable trace 

How does one find a trace file for a concurrent program?

In 11G:

Set Serveroutput ON

DECLARE 
DB_Version VARCHAR2(2);
Trace_location VARCHAR2(240);
BEGIN 
SELECT SUBSTR(vi.version, 1, INSTR(vi.version, '.')-1) INTO DB_Version FROM v$instance vi;

IF TO_NUMBER(DB_Version) >= 11 THEN
/* Following Line included as Execute Immediate as V$DIAG_INFO does not exist in DB Versions prior to 11g */
EXECUTE IMMEDIATE 'SELECT value FROM V$DIAG_INFO WHERE NAME = ''Diag Trace''' INTO Trace_location ;
ELSE 
SELECT value INTO Trace_location FROM v$parameter WHERE name = 'user_dump_dest';
END IF ;
dbms_output.put_line('Trace File should be located in the directory :- '|| trace_location);
END;
/

 Before 11G:

SQL> 

prompt 
accept request prompt 'Please enter the concurrent request id for the appropriate concurrent program:' 
prompt 

column traceid format a8 
column tracename format a80 
column user_concurrent_program_name format a40 
column execname format a15 
column enable_trace format a12 
set lines 80 
set pages 22 
set head off 

SELECT 'Request id: '||request_id , 
'Trace id: '||oracle_Process_id, 
'Trace Flag: '||req.enable_trace, 
'Trace Name: 
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc', 
'Prog. Name: '||prog.user_concurrent_program_name, 
'File Name: '||execname.execution_file_name|| execname.subroutine_name , 
'Status : '||decode(phase_code,'R','Running') 
||'-'||decode(status_code,'R','Normal'), 
'SID Serial: '||ses.sid||','|| ses.serial#, 
'Module : '||ses.module 
from fnd_concurrent_requests req, v$session ses, v$process proc, 
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog, 
fnd_executables execname 
where req.request_id = &request 
and req.oracle_process_id=proc.spid(+) 
and proc.addr = ses.paddr(+) 
and dest.name='user_dump_dest' 
and dbnm.name='db_name' 
and req.concurrent_program_id = prog.concurrent_program_id 
and req.program_application_id = prog.application_id 
and prog.application_id = execname.application_id 
and prog.executable_id=execname.executable_id;