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

May 7, 2006: On creating workspaces and merging/refreshing data with Oracle's Workspace Manager

With Oracle's Workspace Manager it's possible to have several versions of data. That is, data can be changed, thus giving it a new version, without users looking at data of another version seeing the modified data. In this article, I try to explain this feature.
As almost always, I start by creating a table:
create table wm_test_table (
  a number primary key, -- Without primary key: ORA-20133: 
  b varchar2(15)
);
This table is filled with the names of five U.S. cities.
insert into wm_test_table values (1, 'New York');
insert into wm_test_table values (2, 'Las Vegas');
insert into wm_test_table values (3, 'Los Angeles');
insert into wm_test_table values (4, 'Houston');
insert into wm_test_table values (5, 'Seattle');

commit;
Now, I put the table under «version control».
begin
  dbms_wm.enableversioning ('wm_test_table');
end;
/
The data in our versioned table can now be brought into a workspace. The important thing to know about workspaces is: data changed in a workspace is not visible to other workspaces until the data is either merged or refreshed. This is what this article is going to demonstrate.
In order to bring the data into a workspace, a workspace must be created. I'll make things a bit more interesting and create two workspaces: TEST_WS_EUROPE and TEST_WS_ASIA:
begin
  dbms_wm.createworkspace ('TEST_WS_EUROPE');
  dbms_wm.createworkspace ('TEST_WS_ASIA'  );
end;
/
user_workspaces can be used to find out about the workspaces that are owned by me. I now should find the two newly created workspaces:
select workspace, parent_workspace from user_workspaces;
WORKSPACE                      PARENT_WORKSPACE
------------------------------ ------------------------------
TEST_WS_EUROPE                 LIVE
TEST_WS_ASIA                   LIVE
The query shows the two created workspaces. When I created the workspaces, I was in the LIVE workspace which is why their parent is LIVE. The LIVE workspace is the default workspace and always exists, it cannot be deleted or created. When someone logs on, she automatically goes into the LIVE workspace.
I move into the TEST_WS_EUROPE workspace:
begin
  dbms_wm.gotoworkspace('TEST_WS_EUROPE');
end;
/
I didn't change any data, so when I query wm_test_table, I should find what I inserted:
select * from wm_test_table order by a;
         A B
---------- ---------------
         1 New York
         2 Las Vegas
         3 Los Angeles
         4 Houston
         5 Seattle
Within the TEST_WS_EUROPE workspace, I update the table and commit the changes.
update wm_test_table set b='Paris'  where a=2;
update wm_test_table set b='London' where a=4;

commit;
It won't be so much of a surprise that the data, when queried, reflects my changes:
select * from wm_test_table order by a;
         A B
---------- ---------------
         1 New York
         2 Paris
         3 Los Angeles
         4 London
         5 Seattle
I go back to the LIVE workspace...
begin
  dbms_wm.gotoworkspace('LIVE');
end;
/
...and do the same select statement again:
select * from wm_test_table order by a;
         A B
---------- ---------------
         1 New York
         2 Las Vegas
         3 Los Angeles
         4 Houston
         5 Seattle
The rows (a=2 and a=4) that I changed and commited in the TEST_WS_EUROPE do still show the previous values (Las Vegas, Houston). That is, in the TEST_WS_EUROPE, I created a new version of these rows, but when in the LIVE workspace, I still see the old version of these rows.
I change some data within the LIVE workspaces:
update wm_test_table set b='Phoenix' where a=5;

commit;
When I query the data here, it reflects the update:
select * from wm_test_table order by a;
         A B
---------- ---------------
         1 New York
         2 Las Vegas
         3 Los Angeles
         4 Houston
         5 Phoenix
I move to the TEST_WS_ASIA workspace...
begin
  dbms_wm.gotoworkspace('TEST_WS_ASIA');
end;
/
... and do the query here:
select * from wm_test_table order by a;
         A B
---------- ---------------
         1 New York
         2 Las Vegas
         3 Los Angeles
         4 Houston
         5 Seattle
I do not see the changes made in the LIVE workspace nor the changes made in the TEST_WS_EUROPE workspace. The data is the data that was in the LIVE workspace at the time of the creation of the TEST_WS_ASIA workspace.
Here too, I change some data:
update wm_test_table set b='Tokyo'   where a=1;
update wm_test_table set b='Beijing' where a=3;

commit;
I refresh the TEST_WS_EUROPE workspace. A refresh updates the data in the refreshed workspace with the changes made in its parent workspace.
begin
 dbms_wm.refreshworkspace('TEST_WS_EUROPE');
end;
/
Let's see what happened to the data in the TEST_WS_EUROPE workspace:
begin
  dbms_wm.gotoworkspace('TEST_WS_EUROPE');
end;
/
select * from wm_test_table order by a;
         A B
---------- ---------------
         1 New York
         2 Paris
         3 Los Angeles
         4 London
         5 Phoenix
Yes, there is now Phoenix instead of Seattle. However, the changes made in the TEST_WS_ASIA workspace are not refreshed into the TEST_WS_EUROPE workspace, because TEST_WS_ASIA is not the parent workspace of TEST_WS_EUROPE.
I now merge the TEST_WS_ASIA workspace into the LIVE workspace. A merge is the opposite of a refresh: it updates the parent workspace with the changes made to the merged workspace.
begin
  dbms_wm.mergeworkspace('TEST_WS_ASIA');
end;
/
The merge should now have made the changes made in the TEST_WS_ASIA workspace visible to the LIVE workspace. I want to verify it:
begin
  dbms_wm.gotoworkspace('LIVE');
end;
/
select * from wm_test_table order by a;
         A B
---------- ---------------
         1 Tokyo
         2 Las Vegas
         3 Beijing
         4 Houston
         5 Phoenix
Yes, the asian cities are now visible in the LIVE workspace.

Cleaning up

In order to clean up this experiment, I first have to disable versioning on wm_test_table:
begin
  dbms_wm.disableversioning ('wm_test_table', true);
end;
/
Then, I have to remove the workspaces. I must not be in the removed workspace, so I go to the live workspace first:
begin
  dbms_wm.gotoworkspace  ('LIVE'          );
  dbms_wm.removeworkspace('TEST_WS_EUROPE');
  dbms_wm.removeworkspace('TEST_WS_ASIA'  );
end;
/
Finally, I drop the wm_test_table:
drop table wm_test_table;

Links

More on Oracle

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