René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

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

Oracle's Workspace Manager allows to travel back in time, so to speak, to see the data of that point in time to which one travelled. This is posslble because the Workspace Manager keeps different versions of data (See On creating workspaces and merging/refreshing data with Oracle's Workspace Manager).
In the following, I want to demonstrate how this is done.
First, a table is created... 
create table wm_gotodate_test (
  id     number(2)    primary key,
  txt    varchar2(20) not null
);
.. and version enabled. For this example, it is important to specify the VIEW_WO_OVERWRITE flag for the hist parameter: 
begin
  dbms_wm.enableversioning (
    'wm_gotodate_test', 
    'VIEW_WO_OVERWRITE'
  );
end;
/
The table is filled with three rows. The column txt is always in lowercase: 
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;
I wait two seconds... 
begin
  dbms_lock.sleep(2);
end;
/
... and store the current time in an SQL*Plus variable named date_1: 
column now new_value date_1
select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') now from dual;
Again, I wait two seconds: 
begin
  dbms_lock.sleep(2);
end;
/
I then update the value baz to BAZ: 
update wm_gotodate_test set txt = 'BAZ' where id = 3;

commit;
After the update, I wait for another two seconds, store the current time in the variable date_2 and wait two seconds: 
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);
Another update, this time, I change foo into FOO, delete bar and insert a QUX. From now on, I have uppercase txt only in the table. 
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;
Again, I do this wait, variable, wait thing, storing the current time in date_3: 
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);
Now, the cool thing: I will travel back in time. I use dbms_wm.gotodate to specify the date from which I want to see the data. As I have stored the time of the first «revision» of the data in date_1, I can use this variable when calling gotodate: 
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
As can be seen, I get the values that were valid at the time of date_1 (only lowercase words in txt).
Now, I move to date_2: 
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
I see the update from baz to BAZ which was valid at date_2.
Finally, I am going to date_3: 
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
These values are the values that were valid at date_3 (only uppercase words).

Cleaning up

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

drop table wm_gotodate_test;

Links

On creating workspaces and merging/refreshing data with Oracle's Workspace Manager
On solving workspace conflicts

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.

Warning: require(): open_basedir restriction in effect. File(/var/www/virtual/adp-gmbh.ch/forum/comment.inc) is not within the allowed path(s): (/home/httpd/vhosts/renenyffenegger.ch/:/tmp/) in /home/httpd/vhosts/renenyffenegger.ch/adp-gmbh.ch/blog/2006/05/16.php on line 445

Warning: require(/var/www/virtual/adp-gmbh.ch/forum/comment.inc): failed to open stream: Operation not permitted in /home/httpd/vhosts/renenyffenegger.ch/adp-gmbh.ch/blog/2006/05/16.php on line 445

Fatal error: require(): Failed opening required '/var/www/virtual/adp-gmbh.ch/forum/comment.inc' (include_path='.:/home/httpd/vhosts/renenyffenegger.ch') in /home/httpd/vhosts/renenyffenegger.ch/adp-gmbh.ch/blog/2006/05/16.php on line 445