Home > Oracle DBA > Enable the trace for a particular SQL.

Enable the trace for a particular SQL.

June 28, 2016

Today i was asked to collect the trace for a particular SQL.   I did the following way.   I might have done it earlier also, but felt to record again.

 

 

Please generate 10046 level 12 on primary and standby for the query following the below steps

At Primary
========

SPOOL primary.OUT
ALTER SESSION SET TIMED_STATISTICS = TRUE;
ALTER SESSION SET STATISTICS_LEVEL=ALL;
ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED;
ALTER SESSION SET TRACEFILE_IDENTIFIER=’PRIMARY_10046′;
ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT FOREVER,LEVEL 12’;
****YOUR QUERY GOES HERE***********
ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT OFF’;
SPOOL OFF
EXIT
At Standby
==========

SPOOL Standby.OUT
ALTER SESSION SET TIMED_STATISTICS = TRUE;
ALTER SESSION SET STATISTICS_LEVEL=ALL;
ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED;
ALTER SESSION SET TRACEFILE_IDENTIFIER=’STANDBY_10046′;
ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT FOREVER,LEVEL 12’;
****YOUR QUERY GOES HERE***********
ALTER SESSION SET EVENTS ‘10046 TRACE NAME CONTEXT OFF’;
SPOOL OFF
EXIT

Advertisements
Categories: Oracle DBA
%d bloggers like this: