1

I'm having what I suspect is a race-condition in an Oracle application I inherited. Is there any way to see when commits against a table was made? I don't care what was changed (at least not for now), I only want to know if a commit was made within a certain timespan.

Improve this question
4
  • you can use logminer
    – Philᵀᴹ
    Commented Jun 28, 2018 at 22:36
  • Is there no way to do it in sql? It's a fairly locked-down environment, getting access to anything new takes a while...
    – bjelleklang
    Commented Jun 28, 2018 at 22:47
  • Not sure if that helps in your case, but in Oracle you have ORA_ROWSCN pseudo-column that holds the most recent change to the row and can be used to get timestamp (with SCN_TO_TIMESTAMP function) .
    – a1ex07
    Commented Jun 29, 2018 at 1:30
  • @a1ex07 wouldn't that give him the time a transaction started? But maybe that's good enough for the OP.
    – Gerard H. Pille
    Commented Jun 29, 2018 at 8:04

1 Answer 1

Reset to default
2

You may query v$logmnr_contents, as per documentation https://docs.oracle.com/cd/B19306_01/server.102/b14215/logminer.htm#i1016607

SELECT SQL_REDO FROM V$LOGMNR_CONTENTS
 WHERE
 SEG_NAME = 'EMPLOYEES' AND
 SEG_OWNER = 'HR' AND
 OPERATION = 'UPDATE' AND
 DBMS_LOGMNR.MINE_VALUE(REDO_VALUE, 'HR.EMPLOYEES.SALARY') >
 2*DBMS_LOGMNR.MINE_VALUE(UNDO_VALUE, 'HR.EMPLOYEES.SALARY');

But remember that the data is changed immediately, not on commit! Until commit, other sessions see the data as it was before, or receive an error if Oracla can no longer show it ("rollback segment too small").

Improve this answer

Your Answer

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

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