Query contents of Trace files

There are a number of ways to make trace files available to developers, but one simple way is to allow them query the contents of a trace file from SQL*Plus and spool it to a file on their local PC.

The following example creates a locked user with the necessary code to read the contents of specified trace files. This code is then made accessible to public and a directory object pointing to the trace location is created.

CONN / AS SYSDBA

-- Create locked user to hold code.
CREATE USER trace_user IDENTIFIED BY trace_user ACCOUNT LOCK;

-- Create a type and pipelined table function to read the specified trace file.
CREATE TYPE trace_user.t_trace_tab AS TABLE OF VARCHAR2(32767);
/

CREATE OR REPLACE FUNCTION trace_user.get_trace_file (p_trace_file IN VARCHAR2)
  RETURN trace_user.t_trace_tab PIPELINED
AS
  l_file UTL_FILE.file_type; 
  l_text VARCHAR2(32767);
BEGIN
  l_file := UTL_FILE.fopen('TRACE_DIR', p_trace_file, 'r', 32767);
  BEGIN
    LOOP
      UTL_FILE.get_line(l_file, l_text);
      PIPE ROW (l_text);
    END LOOP;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL;
  END;
  UTL_FILE.fclose(l_file);
  RETURN;
EXCEPTION
  WHEN OTHERS THEN
    PIPE ROW ('ERROR: ' || SQLERRM);
    IF UTL_FILE.is_open(l_file) THEN
      UTL_FILE.fclose(l_file);
    END IF;
    RETURN;
END;
/

-- Make the code accessible to PUBLIC.
GRANT EXECUTE ON trace_user.t_trace_tab TO PUBLIC;
GRANT EXECUTE ON trace_user.get_trace_file TO PUBLIC;
CREATE PUBLIC SYNONYM get_trace_file FOR trace_user.get_trace_file;

-- Create a directory object to the trace location.
CREATE OR REPLACE DIRECTORY trace_dir AS '/u01/app/oracle/diag/rdbms/db11g/DB11G/trace/';
GRANT READ ON DIRECTORY trace_dir TO trace_user;

With that in place we can see how a developer would create and return a trace file to their local PC.

-- Make sure the user has access to DBMS_MONITOR.
CONN / AS SYSDBA
GRANT EXECUTE ON dbms_monitor TO hr;

CONN hr/hr

-- Identify the current trace file.
SELECT value
FROM   v$diag_info
WHERE  name = 'Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/db11g/DB11G/trace/DB11G_ora_6309.trc

SQL>

-- Turn of tracing.
EXEC DBMS_MONITOR.session_trace_enable(waits=>TRUE, binds=>FALSE);

-- Do something you wish to trace.
SET LINESIZE 100
COLUMN employee_name FORMAT A30
COLUMN department_name FORMAT A20
COLUMN manager_name FORMAT A30

SELECT e.first_name || ' ' || e.last_name AS employee_name,
       d.department_name,
       e2.first_name || ' ' || e2.last_name AS manager_name
FROM   employees e
       JOIN departments d ON e.department_id = d.department_id
       JOIN employees e2 ON e.manager_id = e2.employee_id
WHERE  d.department_name = 'Finance'
ORDER BY d.department_name;

EMPLOYEE_NAME                  DEPARTMENT_NAME      MANAGER_NAME
------------------------------ -------------------- ------------------------------
Nancy Greenberg                Finance              Neena Kochhar
Luis Popp                      Finance              Nancy Greenberg
Jose Manuel Urman              Finance              Nancy Greenberg
Ismael Sciarra                 Finance              Nancy Greenberg
John Chen                      Finance              Nancy Greenberg
Daniel Faviet                  Finance              Nancy Greenberg

6 rows selected.

SQL> 

-- Turn off tracing.
EXEC DBMS_MONITOR.session_trace_disable;

-- Spool the contents of the relevant trace file to a local file.
SET PAGESIZE 0 FEEDBACK OFF TRIMSPOOL ON TAB OFF
SPOOL C:\tracefile.trc

SELECT *
FROM   TABLE(get_trace_file('DB11G_ora_6309.trc'));

SPOOL OFF
SET PAGESIZE 14 FEEDBACK ON

The developer can now use a local installation of TKPROF or SQL Developer to translate the trace file.

Reference: http://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof.php