Oracle Database 12c -- Access TKPROF Reports as External Table

Problem

You want to access TKPROF reports directly from the database by means of external tables

 

Solution

1. Create a directory on the database host. This directory will be referred as EXEC_DIR further. Create a script in EXEC_DIR. The Oracle user must have read and execute access on the directory and the script. Create the directory object for the folder:

 

tkprofgen.sh

 

#!/bin/bash

<full_path_to>/tkprof $1 /dev/stdout sys=no

 

chmod +x tkprofgen.sh

 

sqlplus> create directory exec_dir as '<EXEC_DIR>';

 

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

select value as DIAG_TRACE from v$diag_info where name = 'Diag Trace';

 

create directory diag_trace as '<DIAG_TRACE>';

 

3. Create external table

create table tkprof_file ( line varchar2(4000) )
organization external
(type oracle_loader
 default directory diag_trace
 access parameters (
  records delimited by newline nologfile nobadfile nodiscardfile
  preprocessor exec_dir:'tkprofgen.sh'
  skip 1
  fields terminated by '!@#$%'
 )

location ('<trace_file>')

)
reject limit unlimited;

Now the TKPROF report can be access directly from the database as an external table.

To change the subjected trace file use:

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