RDBMS version: 11.2.0.4
OS : Oracle Linux 6.5
Between 10 AM and 11 AM somebody ran a delete similar to the following and I wanted to know the details of the user who ran it. Like, which DB User, which Machine, What time, ..etc . By , 11:20 AM , I tried to retrieve this information using methods menthioned below (Attempts 1,2 and 3 ). But, I couldn't.
DELETE FROM tablexyz WHERE curr_trk_code=50;
Using query provided by Andrew Sayer in the following post , I made 3 attempts
https://community.oracle.com/message/14166401#14166401
Attempt1. I tried joining gv$session and gv$sql. But, I couldn't find it
Attempt2. I tried joining gv$session ,gv$active_session_history and gv$sql
Attempt3. I tried querying DBA_HIST_ACTIVE_SESS_HISTORY too by modifying Attempt2's query . Still no luck.
Question1. What exactly is the difference between GV$ACTIVE_SESSION_HISTORY and DBA_HIST_ACTIVE_SESS_HISTORY ?
If I understand correctly, when a session logs out and Instance , it will then become available in GV$ACTIVE_SESSION_HISTORY view.
But, when will that session's info be moved to DBA_HIST_ACTIVE_SESS_HISTORY ?
Question2. Any possible reasons why the above mentioned DELETE statement cannot be found in ASH using Attempts 1, 2 and 3 ?