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
