Re: How to get sql text after run

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_at_gmail.com> 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
>
>

--
http://www.freelists.org/webpage/oracle-l