test_trace

SQL> create or replace procedure test_TRACE is

  2  sql_TEXT VARCHAR2(4000);

  3  cursor cursor_trace IS select u_dump.value || '/' || db_name.value || '_ora_' || v$process.spid ||

  4         nvl2(v$process.traceid, '_' || v$process.traceid, null) || '.trc' "Trace File"

  5    from v$parameter u_dump

  6   cross join v$parameter db_name

  7   cross join v$process

  8    join v$session

  9      on v$process.addr = v$session.paddr

 10   where u_dump.name = 'user_dump_dest'

 11     and db_name.name = 'db_name'

 12     and v$session.audsid = sys_context('userenv', 'sessionid');

 13  begin

 14  open cursor_trace;

 15  loop

 16    fetch cursor_trace into sql_TEXT;

 17    exit when cursor_trace%notfound;

 18    dbms_output.put_line(sql_TEXT);

 19   end loop;

 20  end test_TRACe;

 21  /

Procedure created.

SQL> exec test_TRACE;

PL/SQL procedure successfully completed.

SQL> set serveroutput on

SQL> /

Procedure created.

SQL> exec test_TRACE;

/u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_3255.trc

/u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_2838.trc

PL/SQL procedure successfully completed.