HOME → Worry-Free Access

An Alternative Solution to Provide Worry-Free Access

Taken from "Effective Oracle By Design". Pages 129-135.

I am going to offer another alternative to providing trace file access that will work nicely and that I've used with great success. You do not need to set any undocumented init.ora parameters. You can make it so the developers can access only their trace files. You can remove the need to provide access to the physical server file system. It accomplishes the goal of providing immediate access to the trace files, while at the same time removing many of the concerns of doing so. You can do all of this with a fairly simple PL/SQL set of routines.

What we will do involves many steps, it's like we are creating an "application" and in fact, we are. The first step will be to create a schema with the minimal set of priviledges necessary. This schema will be used to allow users to view trace files as if they were database tables (they can select * from "tracefile" in effect). In order to do this, we'll write a little PL/SQL to read a trace file and make that PL/SQL callable from SQL. We'll also use a LOGOFF trigger in order to capture the trace files that are generated into a database table, along with the "owner" of that trace file so that developers will only see their trace files and not just any trace file. And lastly, we'll develop a SQLPlus script that makes invoking TKPROF against these "database tables that are trace files" as easy as possible.

Create a Schema First, we'll create a schema that will be used to provide access to the trace files owned by the user, as if each trace file were a database table. The end user will be able to select * from their_trace_file . Using SPOOL in SQL*Plus, they can save this trace file locally and not need to have access to the server at all. The user we need will be created with at least these privileges:

create user trace_files identified by trace_files 
default tablespace users quota unlimited on users;

grant create any directory, /* to read user dump dest */
create session ,  /* to log on in the first place */
create table ,    /* used to hold users -> trace files */
create view ,     /* used so users can see what traces they have */
create procedure , /* create the func that gets the trace data */
create trigger ,  /* to capture trace file names upon logoff */
create type ,  /* to create a type to be returned by function */
administer database trigger /* to create the logoff trigger */
to trace_files;

/* these are needed to find the trace file name */
grant select on v_$process to trace_files;
grant select on v_$session to trace_files;
grant select on v_$instance to trace_files;

Create a View and Table Next, we'll create a view that returns the name of the trace file for the current session. As discussed in "Get the Trace Filename" earlier in the chapter, this view may need to be customized for your operating system:

create view session_trace_file_name
as
select d.instance_name || '_ora_' || ltrim(to_char(a.spid)) || 
       '.trc' filename
  from v$process a, v$session b, v$instance d
 where a.addr = b.paddr
   and b.audsid = sys_context( 'userenv', 'sessionid')
/

And then create a table to hold the mappying of usernames to filenames. We'll also keep a TIMESTAMP (use DATE in Oracle 8i and earlier) to see when the trace file session ended. The view is what the end users will use to see which trace files they have available:

create table avail_trace_files
( username   varchar2(30) default user,
  filename   varchar2(512),
  dt         timestamp default systimestamp,
  constraint avail_trace_files_pk primary key(username,filename)
)
organization index
/
create view user_avail_trace_files
as
select * from avail_trace_files where username = user
/
grant select on user_avail_trace_files to public
/

Create a Trigger Now, we'll use a LOGOFF trigger to capture the name of the trace file and the current username, if a trace file actually exists. We'll use a BFILE to achieve this.

create or replace directory UDUMP_DIR
as '/u01/app/oracle/admin/ark/udump'
/

create or replace trigger capture_trace_files
before logoff on database
begin
    for x in ( select * from session_trace_file_name )
    loop
        if ( dbms_lob.fileexists( 
                      bfilename('UDUMP_DIR', x.filename ) ) = 1 )
        then
            insert into avail_trace_files (filename)
            values (x.filename);
        end if;
    end loop;
end;
/

Add the Function Next, we need the function that will read and stream the trace data back to the end user. For this, we will use a pipelined PL/SQL function. In order to do that, we'll need a simple collection type:

create or replace type vcArray as table of varchar2(4000)
/

And then we add the function itself. It begins by issuinga SELECT against USER_AVAIL_TRACE_FILES to make sure that the requested file is available for the currently logged-in user. That SELECT INTO will raise NO_DATA_FILE, which when propagated back to the calling SELECT will just appear as "No data found." If that query suceeds, we'll go on to read the trace file a line at atime and return it to the caller.

create or replace
function trace_file_contents( p_filename in varchar2 )
return vcArray
pipelined
as
    l_bfile       bfile := bfilename('UDUMP_DIR',p_filename);
    l_last        number := 1;
    l_current     number;
begin
    select rownum into l_current
      from user_avail_trace_files
     where filename = p_filename;

    dbms_lob.fileopen( l_bfile );
    loop
        l_current := dbms_lob.instr( l_bfile, '0A', l_last, 1 );
        exit when (nvl(l_current,0) = 0);
        pipe row(
          utl_raw.cast_to_varchar2(
              dbms_lob.substr( l_bfile, l_current-l_last+1,
                                                    l_last ) )
        );
        l_last := l_current+1;
    end loop;
    dbms_lob.fileclose(l_bfile);
    return;
end;
/
grant execute on vcArray to public
/
grant execute on trace_file_contents to public
/

That is it.

Automate the Access So, now that we have this capability to access trace files, how can we use it? We could use a simple script, like this (called tklast.sql perhaps):

column filename new_val f
select filename
  from trace_files.user_avail_trace_files
 where dt = ( select max(dt)
                from trace_files.user_avail_trace_files
            )
/
set termout off
set heading off
set feedback off
set embedded on
set linesize 4000
set trimspool on
set verify off
spool &f
select * from TABLE( trace_files.trace_file_contents( '&f' ) );
spool off
set verify on
set feedback on
set heading on
set termout on
host tkprof &f tk.prf
edit tk.prf

This is all you would need to automate this process. It would find the last trace file for your username, retrieve it, and run TKPROF against it to format it.