1

Someone is updating a record in a custom Oracle table, is there a way to find who is updating the record and or at least the table?

Improve this question

1 Answer 1

Reset to default
2

You can audit updates to the table (assuming that auditing is enabled in your database). Or you can create a trigger on the table that captures the user and the row being updated and writes the results to an audit table. Or you can use the DBMS_LOGMNR package to go through the historical redo logs to see who executed an UPDATE in the past.

In any of these cases, you'll see the Oracle user that did the UPDATE. That may or may not be what you want, however. Most applications these days are three-tier applications where the user that is connecting to the database is shared by many different human users. If your middle tier is handling the process of authenticating users and using a single shared Oracle user account in its connection pools, you would need to have the middle tier record who is doing the UPDATE or you would need for the middle tier to identify to the database who the current application user is for any request via a context, via calls to DBMS_APPLICATION_INFO, via calls to a stored procedure, or by just passing the user in with the UPDATE statement.

Improve this answer
14
  • It is being done, directly on the table, by Toad or SQL plus and I do not have access to create triggers.
    – Kiranshell
    Aug 28, 2012 at 18:05
  • @Kiranshell - If you don't have access to create triggers, does that imply that you also don't have access to enable and configure auditing and that you don't have access to the historical redo logs? If you just have privileges to query the data, you can't get privileges to be able to record who is updating rows, and the people that do have those privileges aren't able to help you, you're probably out of luck.
    – Justin Cave
    Aug 28, 2012 at 18:09
  • can we use V$SESSION_LONGOPS, if we know to what value it is being updated using sql.
    – Kiranshell
    Aug 28, 2012 at 18:13
  • @Kiranshell - Why would you use that? A simple update will not generally cause an entry in V$SESSION_LONGOPS because it is not a long operation. There is some chance that you could actively monitor V$SQL and V$SQL_BIND_CAPTURE and, if you got really lucky, find the session and thus the user that was executing a particular UPDATE statement with a particular bind variable value. But that would generally require quite a bit of luck or quite a bit of patience until you happened to see the particular activity that you're looking for.
    – Justin Cave
    Aug 28, 2012 at 18:16
  • 1
    @Kiranshell - So, you know that someone is going to issue an UPDATE in the future, you know the primary key that they are going to update, but you don't know who is going to do the update? That seems rather odd-- can you give a bit more context? If the UPDATE is being issued from SQL*Plus, I assume it's a one-time event, not a constant process. Is the UPDATE statement using bind variables?
    – Justin Cave
    Aug 28, 2012 at 18:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.