Generate Oracle Trace Files

To generate oracle trace files Use the following below commands:

alter session set sql_trace=TRUE;

Above command will turn the trace session ON

ALTER SESSION SET TRACEFILE_IDENTIFIER='39337675761' EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

Above command will generate the trace file with the number 39337675761.trc file with prefix of  sid_ora_pid_traceid.trc

@ c:\sql_file.sql

Execute the above command.

alter session set sql_trace= false ;

Turn Tracing off with above command.

To find out the location of trace file generated query V$DIAG_INFO. 

For Example:

SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File';

OR

select value
from v$parameter
where name = 'user_dump_dest';