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