How to increase the size of query audit log?

Question

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?

Answer 1

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:

begin
  dbms_workload_repository.modify_snapshot_settings
  (
    retention => 5184000
  );
end;
/

Where 5184000 is 60 days (5184000 minutes).