grant access to trace files

As a developer, you sometimes need to contact your dba to get an user trace. As a dba, sending trace files to developer is not much fun.

But how do you get access to your traces without any dba ?

One way could be to set _trace_files_public=true, but this is bad. It is undocumented, it is unsupported, there is no control to who can access the files, and probably it will not help if you have no access to the database server.

I prefer to provide a function that reads from the trace directory.

Ok, here we go :

CREATE DIRECTORY user_dump_dest AS
'/app/oracle/admin/DB01/udump';

CREATE OR REPLACE FUNCTION get_tracefile (file_name VARCHAR2)
RETURN VARCHAR2
IS
dest_loc CLOB;
src_loc BFILE;
ret VARCHAR2 (4000);
BEGIN
src_loc := BFILENAME ('USER_DUMP_DEST', file_name);
DBMS_LOB.OPEN (src_loc, DBMS_LOB.lob_readonly);
DBMS_LOB.createtemporary (dest_loc, TRUE);
DBMS_LOB.loadfromfile (dest_loc, src_loc, 4000);
ret := DBMS_LOB.SUBSTR (dest_loc, 4000);
DBMS_LOB.CLOSE (src_loc);
RETURN ret;
END;
/


Just a small function that returns the first 4000 characters of the trace file. I could then grant execute on that function to the developers.

it works quite well
SELECT get_tracefile ('db01_ora_6224.trc')
FROM DUAL;
GET_TRACEFILE('DB01_ORA_6224.TRC')
----------------------------------------------------------------------
/app/oracle/admin/DB01/udump/db01_ora_6224.trc
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /app/oracle/product/dbms/DB01
System name: SunOS
Node name: dbsrv01
Release: 5.8
Version: Generic_117000-05
Machine: sun4u
Instance name: DB01
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 6224, image: oracle@dbsrv01 (TNS V1-V3)

*** SESSION ID:(273.54591) 2009-04-27 12:13:57.292
*** 2009-04-27 12:13:57.292
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [17069], [0x3EED05050],
[], [], [], [], [], []
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
ORA-00928: missing SELECT keyword
Current SQL statement for this session:
alter PACKAGE "LSC_PKG" compile body
----- PL/SQL Call Stack -----
...


I should mention than granting access to trace files is a security issue as a hacker could dump some security modules. But it will be fine for your trusted developers.