View SQL History by User [Oracle]

2
votes

I'm trying to find all (available) SQL history by user in our Oracle 11g database. I've tried using some view such as v$sql_monitor, v$sqlarea, and dba_hist_active_sess_history to try to get usernames, and their executed SQL statements (joined on SID and Serial #) but I'm not having any luck. Our senior DBA and DBE said they've done it before but just told me to look in sqlarea since that has the longest history of SQL held. I'm not having any luck with this. Is this possible to do in Oracle?

Edit: We use SQL Developer. I understand that TOAD may or may not have this feature built-in but I haven't been able to find anything that accomplishes this (other than view current sessions and current SQL) in SQL Developer.

2
oracleoracle11g
check this table v$sql – Frank
what do you want exactly - a list of every SQL executed by every user? you're never going to find that. – thatjeffsmith
Your DBA is wrong. V$SQLSTATS has the longest retention, even after the cursor has been aged out of the shared pool. – sandman

2 Answers

0
votes

You can try something like that if you have the proper licensing to query dba_hist_active_sess_history, you need a license for the diagnostic pack:

select trunc(hist.sample_time,'DD'),u.name,hist.sql_id,sql.sql_text
from dba_hist_active_sess_history hist,
     dba_hist_sqltext sql,
     user$ u
where hist.sql_id = sql.sql_id
and   hist.user_id = u.user#;
0
votes

I think the only way to fetch the exact SQL-Executions of a User (i think you mean Session?) is by enabeling Tracing. I think this article (https://oracle-base.com/articles/misc/sql-trace-10046-trcsess-and-tkprof) gives you a good instruction how it is used.