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.