Live ORACLE

DECLARE

  right_now  TIMESTAMP;

   yesterday  TIMESTAMP;

   sometime   TIMESTAMP;

   scn1       INTEGER;

   scn2       INTEGER;

   scn3       INTEGER;

BEGIN

  right_now := systimestamp; -- Get the current SCN

 

  scn1 := Timestamp_to_scn(right_now);

 

  dbms_output.Put_line('Current SCN is '

                       ||scn1);

 

  yesterday := right_now - 1; -- Get the SCN from exactly 1 day ago

 

  scn2 := Timestamp_to_scn(yesterday);

 

  dbms_output.Put_line('SCN from yesterday is '

                       ||scn2);

 

  -- Find an arbitrary SCN somewhere between yesterday and today

  -- In a real program we would have stored the SCN at some significant moment

  scn3 := (scn1 + scn2) / 2;

 

  sometime := Scn_to_timestamp(scn3); -- What time was that SCN was in effect?

 

  dbms_output.Put_line('SCN '

                       ||scn3

                       ||' was in effect at '

                       ||To_char(sometime));

END;

/

Using the SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN Functions