Getting max(ora_rowscn) - 10 minutes


I need to get the data from the table based on when the table was last modified. the load takes ~10 minutes so I would like to get max(ora_rowscn) minus 10 minutes.

select TO_CHAR(SCN_TO_TIMESTAMP(max(ORA_ROWSCN) - 600 ),'YYYY-MM-DD HH24:MI:SS')
FROM table

??



You can convert to and from a timestamp with SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN:

select max(ora_rowscn),
  timestamp_to_scn(scn_to_timestamp(max(ora_rowscn))
    - interval '10' minute) as adj
from <your table>;

  MAX(ORA_ROWSCN)               ADJ
----------------- -----------------
   10320929886378    10320929885515

In some circumstances you might not be able to convert the SCN to a timestamp, if it's too old to look it up; then you'll get something like ORA-08181: specified number is not a valid system change number. Note the bit about SCN to timestamp association in the SCN_TO_TIMESTAMP documentation.

You could possibly bypass the max lookup and just use:

select timestamp_to_scn(systimestamp - interval '10' minute) from dual

... or using the end time of the load process. But you still might not have that association, depending on the time period and your redo retention.

A flashback query might be simpler here anyway, again assuming you're doing this inside the redo retention period:

select * from <your table>
as of timestamp (systimestamp - interval '10' minute);

... or as of any timestamp you want to specify. Having a timestamp column on the table, set by your load process or automatically with a trigger, would be more reliable as it wouldn't rely on redo retention, but perhaps a little slower during the load itself. If this is a one-off check then a flashback query would be simplest though.