1

My application uses Oracle database. At the same time Oracle ERP application and another Windows application (like mine) use same database. Everything looks fine and works synchronized but sometimes the Survey table lose data because of unknown reason!

I thought I delete it mistakenly in a case and added a backup query onto my delete query. Before run delete query, I copy the data to SurveyBackUp table. Yesterday the problem occurred again and I checked my backup table but it was empty! So, it looks like; the lost data is not deleted by my query. Is there any way to detect which query deletes the data?

  • 1
    You could add a trigger on delete and log somewhere at what time it was happening and for what IDs. This might give you a starting point. – Paddy Nov 18 '13 at 12:31
2

Use Oracle's "tool" called Log miner.

  • restore some older backup of your database
  • identify ROWID (physical location) of record which was accidentally deleted
  • restore all archived redo logs
  • use log miner to get the SQL which deleted the row
  • log miner will also tell you culprits program name and hostname
0

See below two posts. It might help you.

It is all about enable auditing and query on meta-data tables in oracle

https://stackoverflow.com/questions/11414216/oracle-queries-executed-by-a-session

https://stackoverflow.com/questions/3237916/how-can-i-see-queries-that-are-executed-against-oracle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.