February 5, 2006: On reading trace files with PL/SQL

connect / as sysdba

create user          tracer 
identified by        tracer 
default tablespace   users 
temporary tablespace temp
quota unlimited on   users;

grant  
      alter  session       ,
      create session       ,
      create procedure     ,
      create sequence      ,
      create any directory ,
      create table         ,
      create trigger       ,
      drop   any directory ,
      create public synonym
to tracer;

grant select on v_$process   to tracer;
grant select on v_$session   to tracer;
grant select on v_$parameter to tracer;
grant select on dba_users    to tracer;

grant execute on utl_file    to tracer;

connect tracer/tracer

create package trace_pck
  authid current_user
as

  max_line_len constant number := 32767;
  cur_line#             number         ; 

  procedure start_   (sql_stmt in varchar2);
  procedure stop__   (sql_stmt in varchar2);
  function  next_line(line    out varchar2) return boolean;

end trace_pck;
/

show errors

create package body trace_pck as

  trace_file_dir  varchar2(250);
  trace_file_name varchar2( 50);
  trace_file      utl_file.file_type;

  chars_read      number;
 
  procedure start_(sql_stmt in varchar2) is begin
    cur_line#  := 0;
    chars_read := 0;

    begin
    execute immediate 'create directory trace_dir as ''' || trace_file_dir || '''';
    exception when others then -- Check if directory already existed.
      if sqlcode != -955 then raise; end if;
    end;

    execute immediate sql_stmt;
  end start_;

  procedure stop__(sql_stmt in varchar2) is begin
    execute immediate sql_stmt;

--  Print directory and name of trace file
--  dbms_output.put_line('Dir:  ' || trace_file_dir);
--  dbms_output.put_line('Name: ' || trace_file_name);

    trace_file := utl_file.fopen('TRACE_DIR', trace_file_name, 'R', max_line_len);
  end stop__;

  function next_line(line out varchar2) return boolean is 
    -- size of newline, might be 1 on some systems
    size_nl constant number := 2;
  begin
    utl_file.get_line(trace_file, line, max_line_len);

    cur_line#  := cur_line#  + 1;
    chars_read := chars_read + nvl(length(line),0) + size_nl;

    return true;

  exception when no_data_found then 

    execute immediate 'drop directory trace_dir';
    return false;

  when others then 

    raise_application_error(-20000, 
      'Error at line: ' || cur_line#       || 
      ' for file: '     || trace_file_name ||
      ' directory: '    || trace_file_dir  ||
      ' chars read: '   || chars_read      ||
      ' message: '      || sqlerrm);

  end next_line;

  begin

    select 
      u_dump .value  ,
      lower(db_name.value)  || '_ora_' ||
      proc   .spid   || 
      nvl2(proc.traceid,  '_' || proc.traceid, null) || '.trc'

    into trace_file_dir,
         trace_file_name

    from 
                 v$parameter u_dump 
      cross join v$parameter db_name
      cross join v$process   proc
            join v$session   sess
              on proc.addr = sess.paddr
    where 
     u_dump .name   = 'user_dump_dest' and 
     db_name.name   = 'db_name'        and
     sess   .audsid = sys_context('userenv','sessionid');

end trace_pck;
/

show errors

grant execute on trace_pck to rene;

connect rene/rene

exec tracer.trace_pck.start_('alter session set sql_trace=true');

select sysdate from dual;

select count(*) from user_tables;

exec tracer.trace_pck.stop__('alter session set sql_trace=false');

set serveroutput on size 1000000

declare
  line varchar2(32767);
begin
  while tracer.trace_pck.next_line(line) loop
    dbms_output.put_line(
      to_char(tracer.trace_pck.cur_line#, '9999') || ': ' || line
    );
  end loop;
end;
/

    1: Dump file d:\oracle\product\10.2.0\admin\ora10r2\udump\ora10r2_ora_1080.trc
    2: Mon Feb 06 00:43:57 2006
    3: ORACLE V10.2.0.1.0 - Production vsnsta=0
    4: vsnsql=14 vsnxtr=3
    5: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    6: With the Partitioning, OLAP and Data Mining options

 [ .. snipped .. ] 

   40: =====================
   41: PARSING IN CURSOR #5 len=24 dep=0 uid=61 oct=3 lid=61 tim=38615721156 hv=2343063137 ad='2ed5bcdc'
   42: select sysdate from dual
   43: END OF STMT
   44: PARSE #5:c=0,e=851,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=38615721151
   45: EXEC #5:c=0,e=59,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=38615721557

 [ .. snipped .. ] 

  362: =====================
  363: PARSING IN CURSOR #6 len=32 dep=0 uid=61 oct=3 lid=61 tim=38615820213 hv=2164565686 ad='2eef23c0'
  364: select count(*) from user_tables
  365: END OF STMT
  366: PARSE #6:c=93750,e=96244,p=0,cr=79,cu=0,mis=1,r=0,dep=0,og=1,tim=38615820206

 [ .. snipped .. ]