Simple example: tracefiles for the last 5 days:
select fc.*
from v$diag_trace_file f
join v$diag_trace_file_contents fc
on f.adr_home=fc.adr_home
and f.trace_filename=fc.trace_filename
where f.modify_time >= systimestamp - interval'5' minute
and fc.timestamp >= systimestamp - interval'5' minute
and fc.component_name = 'SQL_Trace'
--and fc.section_name like 'kests%'
;
--or:
select tr.*
from v$diag_app_trace_file tf,
v$diag_sql_trace_records tr
where tf.sql_trace = 'Y'
and tf.modify_time > systimestamp - interval'5'minute
and tr.adr_home = tf.adr_home
and tr.trace_filename = tf.trace_filename
and tr.timestamp > systimestamp - interval'5'minute;