0

Background

User is claiming that records are deleted from a specific table, but nobody knows why. They are not sure if it's an application error or done by a user.

Question

Is it possible to monitor / catch / trace delete statements executed against a specific table?

My first idea was to create an event monitor, but I can't limit it to a specific table or a specific kind of statement.

0

Use the AUDIT TABLE statement on this table using an AUDIT POLICY with the EXECUTE category.
Or create an AFTER UPDATE trigger.

  • This is a good idea, but it as far as I see it's also not possible to limit the monitoring to delete statements only. Or do I miss something? – Cyberider Feb 22 at 9:55
  • If you mean auditing delete statements only, then yes, it's not possible. – Mark Barinstein Feb 22 at 10:20
  • Yes, that's what I mean. Thanks a lot for your suggestions. I'm pretty sure I can use it sometimes. – Cyberider Feb 26 at 6:31
0

Try a USAGE LIST

A usage list is a database object that records each DML statement section that references a particular table or index.

  • 1
    The MON_GET_TABLE_USAGE_LIST function doesn't have an information about the user or application issued the statement. There is no even a statement text there. Only executable_id which can be used to find the statement text, if it's still in the package cache or in an event monitor for package cache, if the latter has been created and is active. – Mark Barinstein Feb 26 at 7:05
  • Yes. As Mark points out, you would need to take the executable_id and get the statement text from e.g. MON_GET_PKG_CACHE_STMT or elsewhere – Paul Vernon Feb 27 at 10:17

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.