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.
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.
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
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;
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;
