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

Oracle Workspace Manager, Example 1

select dbms_wm.getworkspace from dual;
The function getworkspace should return LIVE. 
GETWORKSPACE
----------------------------------
LIVE

create user wm_user
identified by pw_user
default tablespace ts_data
temporary tablespace ts_temp;

alter user wm_user quota unlimited on ts_data;

grant connect, create table to wm_user;

grant execute on dbms_lock to wm_user;

begin
  dbms_wm.grantSystemPriv (
    'ACCESS_ANY_WORKSPACE,    ' ||
    'MERGE_ANY_WORKSPACE,     ' ||
    'CREATE_ANY_WORKSPACE,    ' ||
    'REMOVE_ANY_WORKSPACE,    ' ||
    'ROLLBACK_ANY_WORKSPACE'  ,
    'WM_USER'                 ,
    'YES');
end;
/
Connecting as wm_user (that has previously been defined: 
connect wm_user/pw_user
Creating some table on which the workspace manager's abilities will be demonstated.
These table need to have a primary key, otherwise, an ORA-20133: table 'WM_USER.WM_1' needs to have a primary key. 
create table wm_1 (
  a number primary key,  -- Prevent ORA-20133: table 'RENE.WM_1' needs to have a primary key
  b varchar2(20)
);

create table wm_2 (
  c number primary key,              -- Prevent ORA-20133
  d varchar2(20)
);
Now, going to tell the system which tables will be version enabled. The flag VIEW_WO_OVERWRITE means view without overwrite. 
begin
  dbms_wm.enableVersioning('wm_1','VIEW_WO_OVERWRITE');
  dbms_wm.enableVersioning('wm_2','VIEW_WO_OVERWRITE');
end;
/
Now, I insert 4 rows into wm_1 and 1 row into wm_2. If I have a join on a and d, only one record will be returned. 
insert into wm_1 values (1,'one');
insert into wm_1 values (2,'two');
insert into wm_1 values (3,'three');
insert into wm_1 values (4,'four');

insert into wm_2 values (3,'three');
commit;
After having inserted some values, we keep the current time in the SQL*Plus user variable dt_1. This will make it possible to revert to the data as it as of dt_1. 
var dt_1 varchar2(21)

begin
  select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') into :dt_1 from dual;
end;
/
Now, let some time (10 seconds) pass.... 
exec dbms_lock.sleep(10);
This time, I insert three rows into wm_2, so that a join on a and c will return two rows: 
insert into wm_2 values (4,'four');
insert into wm_2 values (5,'five');
insert into wm_2 values (6,'six');

commit;
Let's see, what we have in the tables: 
select * from wm_1 join wm_2 on a=c;
As expected, we get two rows. 
         A B                             C D
---------- -------------------- ---------- --------------------
         3 three                         3 three
         4 four                          4 four
Now, returning in time to the time saved in dt_1: 
begin
  dbms_wm.gotoDate(to_date(:dt_1, 'dd.mm.yyyy hh24:mi:ss'));
end;
/
And doing the same query again: 
select * from wm_1 join wm_2 on a=c;
As we can see, we truly made a time warp jump and the query only returns one row. 
         A B                             C D
---------- -------------------- ---------- --------------------
         3 three                         3 three
Cleaning up: 
commit;

begin
  dbms_wm.disableVersioning('wm_1');
  dbms_wm.disableVersioning('wm_2');
end;
/

drop table wm_1;
drop table wm_2;