Historical SQL queries

Question

Is there an table on oracle database where historical SQL queries executed by users are stored?

Tried with v$sql, v$sqlstats, but couldn't find anything useful.

Answer 1

Try dba_hist_sqltext; join it to dba_hist_snapshot for some more info.

select *
from dba_hist_sqltext t join dba_hist_snapshot s on t.dbid = t.dbid;

Answer 2

The V$ views report what is currently cached in the SGA, therefore they are not reliable as an audit trail.

The details in the DBA_HIST_ views (DBA_HIST_ACTIVE_SESS_HISTORY, DBA_HIST_SQLTEXT, DBA_HIST_SQLSTAT, DBA_HIST_SNAPSHOT etc) persist for longer as they are captured from their v$ equivalents as part of the Automatic Workload Repository feature (AWR), so make sure you are licensed for it to avoid a surprise bill.