CODEWITHSUNDEEP
oracle-databaseoracle11g
Rajeev
Rajeev

Reputation: 1196

How to check whether a delete has been occured in a table at specified time

Recently, a very strange scenario has been reported from one of of our sites. Based on our fields, we found that there should be some kind of delete that must have happenend for that scenario

In our application code, there is no delete for that table itself. So we checked in gv$sqlarea(since we use RAC) table whether there are any delete sql for this table. We found nothing.

Then we tried to do the same kind of delete through our PL/SQL Developer. We are able to track all delete through gv$sqlarea or gv$session. But when we use below query, lock, edit and commit in plsql developer, there is no trace

select t.*, t.rowid 
  from <table>

Something which we are able to find is sys.mon_mods$ has the count of deletes. But it is not stored for a long time, so that we can trace by timestamp

Can anyone help me out to track this down

Oracle Version: 11.1.0.7.0

Type : RAC (5 instances)

Upvotes: 1

Views: 5983

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

gv$sqlarea just shows the SQL statements that are in the shared pool. If the statement is only executed once, depending on how large the shared pool and how many distinct SQL statements are executed, a statement might not be in the shared pool very long. I certainly wouldn't expect that a one-time statement would still be in the shared pool of a reasonably active system after a couple hours.

Assuming that you didn't enable auditing and that you don't have triggers that record deletes, is the system in ARCHIVELOG mode? Do you have the archived logs from the point in time where the row was deleted? If so, you could potentially use LogMiner to look through the archived logs to find the statement in question.

Upvotes: 2

Related Questions