Oracle 11g R2 – Understanding Commitlog

oracleoracle-11g-r2

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.

Best Answer

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").

Related Question