Re: How to get sql text after run

Hi Jonathan.
The trace is the way to get ALL sql_id.
My doubt is resolved.
Thanks for The answer.
Eriovaldo

Em seg, 12 de jul de 2021 05:16, Jonathan Lewis <jlewisoracle_at_gmail.com> escreveu:

>
> 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