This content has been marked as final. Show 3 replies
welcome to the forum!
1) you can use AWR (DBA_HIST_%) and ASH (DBA_HIST_ACTIVE_SESS_HISTORY) views to find some historic information. Unfortunately, it won't go back as long as 3 or 4 months unless you specifically configured the retention period so. Plus, if your UPDATE was using bind variables, most likely you won't be able to retrieve the values (although you may try digging DBA_HIST_SQLBIND
2) you can query your audit trail, but you need audit enabled and configured for that
3) you can dig your old logs using Log Miner, assuming that your database preserves archived logs
In order to prevent such incidents in the future, you need to have a structured security model in your database. Users shouldn't be able to UPDATE database tables on production outside an API.
Use this query to get last 3 month queries order by execution.
select * from (select module,sql_id,round(sum(CPU_TIME_DELTA)/1000000) "CPUTIME(S)",round(sum(ELAPSED_TIME_DELTA)/1000000) "E
LPSED(S)",round(sum(EXECUTIONS_DELTA)) "NUMOFEXEC",round(round(sum(ELAPSED_TIME_DELTA))/1000000)/(round(sum(EXECUTIONS_DELTA))) "Per Execution",sum(ROWS_PROCESSED_DELTA) "NUMOFROW",sum(DISK_READS_DELTA)/1024/1024 "DISK
READ(MB)",sum(BUFFER_GETS_DELTA)/1024/1024 "BUFFERREAD(MB)" from dba_hist_sqlstat where (snap_id between (select min(snap_id)
from dba_hist_snapshot where trunc(begin_interval_time)=trunc(sysdate-92 )) and (select max(snap_id) from dba_hist_snapshot
where trunc(begin_interval_time)=trunc(sysdate ))) and EXECUTIONS_DELTA>10 group by module,sql_id order by "NUMOFEXEC" desc
) where rownum < 51;