I need to find the queries run against an Oracle instance.
On running the query:
SELECT
first_load_time,
sql_id,
sql_fulltext
FROM
v$sql
ORDER BY
first_load_time ASC;
Only records from yesterday are displayed.
Running:
SELECT
*
FROM
dba_hist_sqltext;
Gives absolutely nothing.
What can I do to get sql history over a longer period of time, say 2 months?
Best Answer
For
DBA_HIST_SQLTEXT
, you need Enterprise Edition with the Diagnostic Pack. If you have that licensed, you can enable collection of historical data with:alter system set control_management_pack_access='DIAG';
You can control the retention time of this data with:
Where
5184000
is 60 days (5184000 minutes).