Skip to main content
Kofax

MarkView - Where are database trace files created?

Summary 

4392

If I use a statement similar to the following to turn database tracing on, where is the trace file created?

alter session set sql_trace true;

select count(*) from sf_pending_user_items
where user_id = 'JSMITH'
    and function_id = 2;

alter session set sql_trace false;

To ensure your trace can be identified (Trace_sf_pending_user_items), and to include bind variable values, you can use a script similar to the following:

set serveroutput on size 50000

spool Trace_sf_pending_user_items.txt;
select name, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') start_time from v$database;
alter session set tracefile_identifier=Trace_sf_pending_user_items;
alter session set sql_trace true;
alter session set timed_statistics=true;
alter session set events '10046 trace name context forever, level 12';

select count(*) from sf_pending_user_items
where user_id = 'JSMITH'
    and function_id = 2;
alter session set sql_trace false;
select name, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') end_time from v$database;
spool off;

Database trace files are created on the database server itself (not on your desktop), in the directory specified by the user_dump_dest parameter of the server's initialization file (or spfile).

select * from v$parameter
​​​​​​​where name = 'user_dump_dest';

After the trace is generated - you would need to then connect directly to the database server, cd to the directory specified by 'user_dump_dest' parameter value, and the trace file should be there.

Raw trace files aren't very readable, so it's best to run them through tkprof to get a readable format before providing them to Kofax Support.

Keywords: trace