Database, Logging, SQL and PL/SQL

How to enable tracing in Oracle DB? How to read trace files?

There are many ways to enable tracing in Oracle DB, some are legacy and deprecated. One of latest and recommended method for 11gR2 – use of DBMS_MONITOR (find out more about it in Oracle reference) package.

  • To enable whole instance tracing:

execute dbms_monitor.database_trace_enable(waits => true, binds => true,¬† instance_name => ‘instance_name’);

  • To disable whole instance tracing:

¬†execute dbms_monitor.database_trace_disable(instance_name => ‘instance_name’);

  • To enable current session tracing:

execute dbms_monitor.session_trace_enable(null,null, true, true);

  • To disable current session tracing:

execute dbms_monitor.session_trace_disable(null,null);

Note: procedure parameters can be adjusted to enable particular session or do other tuning.

  • In order to find desired tracing file easier, its recommended to set trace file identifier for current session:

alter session set tracefile_identifier =”any_string”;

  • To check what are enabled traces at the moment:

select * from dba_enabled_traces

  • To check/set where trace files are persisted in the server:

show parameter user_dump_dest;
alter system set user_dump_dest=’/u01/app/oracle/diag/……./trace’;

  • Trace files for need to be processed by TKPROF utility to be more understandable and readable. Just execute a command line in the server:

TKPROF source_file.trc destination_file.txt

TKPROF can be tuned a bit. Find out more in Oracle guide.