Oracle Learning----Trace Files

1. View V$diag_info view, can see the location of the trace file, the specific name and other information.

Name column, Diag Trace is where the 11g trace file is located

The Default trace file is the trace file name of the current session

2. Query can generate trace file name
Select
c.value| | ' \ ' | | d.instance_name| | ' _ora_ ' | | a.spid| | '. TRC ' | |
Case where E.value is isn't null then ' _ ' | | E.value End as Trace
From v$process a,v$session b,v$parameter c,v$instance d,v$parameter E
where a.addr=b.paddr
and b.audsid=userenv (' SessionID ')
and C.name= ' User_dump_dest '
and E.name= ' Tracefile_identifier ';
Note: Under Linux '/' under Windows ' \ '

3. Enable trace files and generate trace files
EXEC dbms_monitor.session_trace_enable

4. Set some identification for the trace file, so that you can find the trace file when you do not have access to the v$process v$session
Alter session set tracefile_identifier= ' Look_for_me ';

Sql> alter session set tracefile_identifier= ' Look_for_me ';

The session has changed.

sql> exec dbms_monitor.session_trace_enable;

The PL/SQL process has completed successfully.

Sql> Select
2 c.value| | ' \ ' | | d.instance_name| | ' _ora_ ' | | a.spid| | '. TRC ' | |
3 case when E.value was not null and then ' _ ' | | E.value End as Trace
4 from v$process a,v$session b,v$parameter c,v$instance d,v$parameter E
5 Where A.ADDR=B.PADDR
6 and B.audsid=userenv (' SessionID ')
7 and C.name= ' User_dump_dest '
8 and E.name= ' Tracefile_identifier ';

TRACE
---------------------------------------------------------------------------

D:\app\administrator\diag\rdbms\roc\roc\trace\roc_ora_4848.trc_look_for_me

Oracle Learning----Trace Files