0

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?

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.