Monitoring which statement updates (and when) a certain table row with Oracle 10

Question

Hi all,

I'm using (have to) a badly designed Oracle(10) DB, for which I don't have admin rights (although I can create tables, triggers, etc in my scheme).

Now I had run into a problem: this DB connected with several users/programs. I must find out who updates a certain row, when, and if possible: with what kind of statement. Is it possible?

Thanks in advance!

Answer 1

I would create a journal table for the table you are working with. It will show you the operation type and the oracle user...as well as a bunch of other data if you need it.

Answer 2

It would be easier to do this if you had admin rights to enable auditing. Without the power of auditing you are left with the use of triggers to handle the logging of inserts/updates/delete. In your case since you are interested in only update, you can put a trigger on the table to fire after the update which logs to another table what was changed, by whom, from where and to what and on what day.