0

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?
Monolithguy
  • 313
  • 6
  • 17
  • 1
    You can view a trace file as an external table, if your DBAs are willing to create a directory object against the trace directory, and you know the file name - listing files is doable but a bit more work and needs a preprocessor script, or a Java stored procedure maybe. But I'd start by seeing if you can even get a directory. – Alex Poole Oct 04 '22 at 15:08
  • You can get it in 12.2 using `v$diag_trace_file` and `v$diag_trace_file_contents` views. All you need is grants on select from these views. – Andrew Klimov Oct 04 '22 at 17:19

1 Answers1

2

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
Chris Saxon
  • 9,105
  • 1
  • 26
  • 42