Are you hoping to find every SQL statement that ran in a time interval in
the past?
There's no way to do that from views.
dba_hist_active_sess_history will give you a sample of the SQL_IDs that
were active in the interval and you may be able to find the SQL from the
sql_id in various places. But it's only a sample.
dba_hist_sqlstat will report the sql_ids (and various workload details) for
"interesting" SQL that was in the library cache as each snapshot was taken.
(join to dba_hist_snapshot to convert snap_id to date/time). But it's only
a sample again, and won't necessarily capture all the "interesting" SQL
because some of it may have been flushed from the library cache in the
interval.
gv$sqlarea is only a summary of statements and cumulative workloads that
have not had their parent cursors flushed from the library cache.
If you really want to capture all the SQL then a system wide (or maybe
session logon trigger) to enable a low level of SQL tracing is about the
only thing you can do - unless you enable some sort of execution audit
through (e.g. FGA).
Regards
Jonathan Lewis
On Sat, 10 Jul 2021 at 23:39, Eriovaldo Andrietta <ecandrietta@xxxxxxxxx>
wrote:
Hi all,
What is the best Oracle 12.2.x view in order to retrieve the full sql_text
that ran some hours ago ?
I usually try to get the in the sequence :
GV$SQLAREA
DBA_HIST_ACTIVE_SESS_HISTORY
Is there another view that surely I can get the sql_text using a datetime
interval ?
Regards
Eriovaldo
