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.

create table wm_test_table (
  a number primary key, -- Without primary key: ORA-20133: 
  b varchar2(15)
);

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;

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.

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

begin
  dbms_wm.gotoworkspace('LIVE');
end;
/

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.

update wm_test_table set b='Phoenix' where a=5;

commit;

select * from wm_test_table order by a;

         A B
---------- ---------------
         1 New York
         2 Las Vegas
         3 Los Angeles
         4 Houston
         5 Phoenix

begin
  dbms_wm.gotoworkspace('TEST_WS_ASIA');
end;
/

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.

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;
/

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

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;
/

drop table wm_test_table;

Links


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

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

Cleaning up

Links

More on Oracle