May 16, 2006: On travelling back in time with Oracle's Workspace Manager

create table wm_gotodate_test (
  id     number(2)    primary key,
  txt    varchar2(20) not null
);

begin
  dbms_wm.enableversioning (
    'wm_gotodate_test', 
    'VIEW_WO_OVERWRITE'
  );
end;
/

insert into wm_gotodate_test values (1, 'foo');
insert into wm_gotodate_test values (2, 'bar');
insert into wm_gotodate_test values (3, 'baz');

commit;

begin
  dbms_lock.sleep(2);
end;
/

column now new_value date_1
select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') now from dual;

begin
  dbms_lock.sleep(2);
end;
/

update wm_gotodate_test set txt = 'BAZ' where id = 3;

commit;

exec dbms_lock.sleep(2);

column now new_value date_2
select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') now from dual;

exec dbms_lock.sleep(2);

update wm_gotodate_test set txt = 'FOO' where id = 1;

delete from wm_gotodate_test where id = 2;
insert into wm_gotodate_test values (4, 'QUX');

commit;

exec dbms_lock.sleep(2);

column now new_value date_3
select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') now from dual;

exec dbms_lock.sleep(2);

exec dbms_wm.gotodate(to_date('&date_1', 'dd.mm.yyyy hh24:mi:ss'));
select * from wm_gotodate_test;

        ID TXT
---------- --------------------
         1 foo
         2 bar
         3 baz

exec dbms_wm.gotodate(to_date('&date_2', 'dd.mm.yyyy hh24:mi:ss'));
select * from wm_gotodate_test;

        ID TXT
---------- --------------------
         1 foo
         2 bar
         3 BAZ

exec dbms_wm.gotodate(to_date('&date_3', 'dd.mm.yyyy hh24:mi:ss'));
select * from wm_gotodate_test;

        ID TXT
---------- --------------------
         3 BAZ
         1 FOO
         4 QUX

begin
  dbms_wm.disableversioning('wm_gotodate_test');
end;
/

drop table wm_gotodate_test;