Oracle Database 12c -- Access SQL Trace Files As An External Table

Problem

You want to access generated SQL Trace files directly from the database by means of external tables

 

Solution

1. Identify location of the trace files directory. This folder will be referred as DIAG_TRACE. Create the directory object for the trace folder

SQL>

col diag_trace new_val diag_trace
select value as DIAG_TRACE from v$diag_info where name = 'Diag Trace';
create directory diag_trace as '&diag_trace'; 

 

3. Create external table

col trace_file new_val trace_file
select substr(value,instr(value,'/',-1)+1) as trace_file from v$diag_info where name = 'Default Trace File';

create table trace_file ( line varchar2(4000) )
organization external
(type oracle_loader
 default directory diag_trace
 access parameters (
  records delimited by newline nologfile nobadfile nodiscardfile
  fields terminated by '!@#$%'
 )
location ('&trace_file')
)
reject limit unlimited;

To change the subjected trace file use:

alter table tkprof_file location('<another_trace_file>');