Monday, March 15, 2021

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


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


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:
<full_path_to>/tkprof $1 /dev/stdout sys=no
chmod +x
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:''
  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>');

No comments: