I need to do some profiling inside a stored procedure hooked up to a fast refresh on commit materialized view. Trace is the only way to get the detail I need.

I have all the permissions I need except for access to the filesystem. To access the filesystem and retrieve my tracefile (as it stands now), I need to go through 2 layers of barebones nonproduction managed services. This means waiting for hours to get my tracefile back to me.

1. Is there any way to route trace results to anything other than the filesystem?
2. Is there any clever way to access the trace directory through the Oracle instance?

CodePudding user response：

You can access this by querying v$diag_trace_file_contents. Find the file name for the current session by getting the Default Trace File from v$diag_info:

alter session set sql_trace = true;

select /* find me */* from dual;

alter session set sql_trace = false;

select payload
from   v$diag_trace_file_contents
where  trace_filename = (
  select substr (
           value,
           instr ( value, '/', -1 )   1
         ) filename
  from   v$diag_info
  where  name = 'Default Trace File'
)
and    payload like '%find me%'
order  by line_number;

PAYLOAD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
-------------------------------
select /* find me */* from dual