Search code examples
oracle-databaseloggingreportawr

oracle - Session execute a specified query

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 !

Solution

  • 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.