0

Today, someone in my system has updated unexpected statement. So that makes my system run incorrect. Now, I would like to see log who (or which session) did it. May I find it in AWR report ? And if I can find it in AWR report, where is it particularly ? Thanks so much !

Leo Le
  • 815
  • 3
  • 13
  • 33

1 Answers1

1

The change could be in many sources, depending on how it was made. Only the last option, Log Miner, will give you exactly everything you want. But it also requires the most effort. Some sources won't tell you the session, but maybe just seeing the relevant SQL will be enough to figure out who did it.

  1. V$SQL - All SQL statements go in there, but they age out of the shared pool so you need to search quickly. If they used a unique query you may be able to find it with something like select * from v$sql where lower(sql_text) like '%table_name%';.
  2. AWR - You may be able to find the SQL in select * from dba_hist_sqltext where lower(sql_text) like '%table_name%';, and then if you're lucky you can find out some session information from select * from dba_hist_active_sess_history where sql_id = '<sql id>';. Active Session History only samples activity, if the query ran very quickly there's a good chance it won't be in there.
  3. Flashback query - If you're lucky the UNDO is still around and you can see exactly how it changed from a flashback query. This may give you the exact time, and what changed. select VERSIONS_STARTSCN, VERSIONS_STARTTIME, VERSIONS_ENDSCN, VERSIONS_ENDTIME, VERSIONS_XID, VERSIONS_OPERATION, your_table.* from your_table versions between scn minvalue and maxvalue;
  4. Log Miner - I haven't used this, but supposedly it's the perfect tool for this job. Read more about it in the documentation.
Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • I find that V$SQL is the most suitable in my situation. However, I can't find who or which session executed query. Maybe is it SERVICE column ? – Leo Le Mar 07 '14 at 07:49
  • That information is not stored in V$SQL. When it's executing you can find the SQL_ID in V$SESSION. But in general that type of information is only stored in the REDO logs, which are what Log Miner reads. It *may* be in some other sources, but only by luck. Oracle couldn't afford to log all that information twice. – Jon Heller Mar 07 '14 at 14:44