Wednesday, December 28, 2011

Track history of changes with Oracle Workspace Manager

Oracle Workspace Manager is a feature introduced in Oracle9i that versions the relational data with no modifications to application
DML operation in a transparent and secure fashion.
It permits simultaneous read and write access to the same prod/dev data.
It can be used to
- Improve concurrency for database updates that complete over days, weeks or months by managing them in workspaces
- Track the history of changes to data and view the database as it existed at any point in time
- Create multiple data scenarios in separate workspaces for what-if analysis

This note here goes in brief over how to version a table to track history of changes and
how you can query data at a previous point in time within your workspace.
This test cases are done in 11gR2.

SQL> show user
USER is "SYS"
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> create user hb identified by hb;
User created.
SQL> grant connect, resource, create table to hb;
Grant succeeded.
SQL> grant select_catalog_role to hb;
Grant succeeded.
SQL> EXECUTE DBMS_WM.GrantSystemPriv ('CREATE_ANY_WORKSPACE','HB','YES');
PL/SQL procedure successfully completed.
SQL> conn hb/hb
Connected.
SQL> show user
USER is "HB"
SQL> select count(*) from user_objects;
  COUNT(*)
----------
         0
        
----------CASE1
We create a table and attempt to version enable it.
In order to do so, we must have a primary key defined on it, otherwise will fail, as shown below:

SQL> create table table1(t1_id1 number(9), t1_details varchar2(30));
Table created.
SQL> exec dbms_wm.enableversioning('table1','view_wo_overwrite');
BEGIN dbms_wm.enableversioning('table1','view_wo_overwrite'); END;
*
ERROR at line 1:
ORA-20133: table 'HB.TABLE1' needs to have a primary key
ORA-06512: at "WMSYS.LT", line 9175
ORA-06512: at line 1
SQL> alter table table1 add constraint pk_t1 primary key (t1_id1);
Table altered.
SQL> select object_name, object_type from user_objects;
OBJECT_NAME                                                                                                              OBJECT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- -------
PK_T1                                                                                                                    INDEX
TABLE1                                                                                                                   TABLE
SQL> exec dbms_wm.enableversioning('table1','view_wo_overwrite');
PL/SQL procedure successfully completed.

Now we will check what other objects are created in order to support version enabling of our table.
We see many views are created, our main table is now a view, but must important, 3 triggers are created:

SQL> select object_name, object_type from user_objects;
OBJECT_NAME                                                                                                              OBJECT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- --------
PK_T1                                                     INDEX
TABLE1_BASE                                     VIEW
TABLE1_LT                                          TABLE
TABLE1                                                 VIEW
TABLE1_MW                                        VIEW
TABLE1_CONS                                    VIEW
TABLE1_AUX                                      TABLE
AUX_TABLE1                                      INDEX
TABLE1_AP1$                                     INDEX
TABLE1_AP2$                                     INDEX
TABLE1_HIST                                     VIEW
TABLE1_BPKC                                   VIEW
TABLE1_PKC                                      VIEW
TABLE1_CONF                                   VIEW
TABLE1_PKDB                                   VIEW
TABLE1_PKDC                                   VIEW
TABLE1_PKD                                      VIEW
TABLE1_DIFF                                     VIEW
TABLE1_LOCK                                  VIEW
TABLE1_TI$                                       INDEX
TABLE1_PKI$                                    INDEX
OVM_INSERT_2                                TRIGGER
OVM_UPDATE_2                              TRIGGER
OVM_DELETE_2                              TRIGGER
24 rows selected.

We insert a couple of rows:

SQL> insert into table1 values (1,'first value');
1 row created.
SQL> insert into table1 values (2,'second value');
1 row created.
SQL> insert into table1 values (3,'third value');
1 row created.
SQL> commit;
Commit complete.
SQL> select t1_id1,t1_details from table1;
    T1_ID1 T1_DETAILS
---------- ------------------------------
         1 first value
         2 second value
         3 third value
SQL> desc table1_lt;
 Name                                                                                                      Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- --------------------
--------------------------------------------------
 T1_ID1                                                           NUMBER(9)
 T1_DETAILS                                                VARCHAR2(30)
 VERSION                                                      NUMBER(38)
 CREATETIME                                             TIMESTAMP(6) WITH TIME ZONE
 RETIRETIME                                               TIMESTAMP(6) WITH TIME ZONE
 NEXTVER                                                    VARCHAR2(500)
 DELSTATUS                                                NUMBER(38)
 LTLOCK                                                      VARCHAR2(100)

SQL> select T1_ID1,T1_DETAILS,VERSION,to_char(CREATETIME,'dd-mon-yyyy hh24:mm:ss') create_time,to_char(RETIRETIME,'dd-mon-yyyy hh24:mm:ss') retiretime
 from table1_lt;
    T1_ID1 T1_DETAILS                        VERSION CREATE_TIME          RETIRETIME
---------- ------------------------------ ---------- -------------------- --------------------
         1 first value                             0 27-dec-2011 17:12:00
         2 second value                            0 27-dec-2011 17:12:13
         3 third value                             0 27-dec-2011 17:12:24

We do some changes to the data in order to be able to track the history:
        
SQL> update table1 set t1_details='after first update';
3 rows updated.
SQL> commit;
Commit complete.
SQL> select T1_ID1,T1_DETAILS,VERSION,to_char(CREATETIME,'dd-mon-yyyy hh24:mm:ss') create_time,to_char(RETIRETIME,'dd-mon-yyyy hh24:mm:ss') retiretime
 from table1_lt;
    T1_ID1 T1_DETAILS             VERSION CREATE_TIME          RETIRETIME
---------- ------------------------------ ---------- -------------------- --------------------
         1 first value                    0 27-dec-2011 17:12:00 27-dec-2011 17:12:11
         2 second value               0 27-dec-2011 17:12:13 27-dec-2011 17:12:11
         3 third value                  0 27-dec-2011 17:12:24 27-dec-2011 17:12:11
         1 after first update          0 27-dec-2011 17:12:11
         2 after first update          0 27-dec-2011 17:12:11
         3 after first update          0 27-dec-2011 17:12:11
6 rows selected.
SQL> delete from table1 where t1_id1=3;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select T1_ID1,T1_DETAILS,VERSION,to_char(CREATETIME,'dd-mon-yyyy hh24:mm:ss') create_time,to_char(RETIRETIME,'dd-mon-yyyy hh24:mm:ss') retiretime
, delstatus from table1_lt;
    T1_ID1 T1_DETAILS  VERSION CREATE_TIME  RETIRETIME  DELSTATUS
---------- ------------------------------ ---------- -------------------- -------------------- ----------
         1 first value              0 27-dec-2011 17:12:00 27-dec-2011 17:12:11    10
         2 second value         0 27-dec-2011 17:12:13 27-dec-2011 17:12:11    10
         3 third value            0 27-dec-2011 17:12:24 27-dec-2011 17:12:11     10
         1 after first update       0 27-dec-2011 17:12:11                              11
         2 after first update       0 27-dec-2011 17:12:11                              11
         3 after first update    0 27-dec-2011 17:12:11 27-dec-2011 17:12:37     11
         3 after first update       0 27-dec-2011 17:12:37                             -12
7 rows selected.
SQL> select * from table1;
    T1_ID1 T1_DETAILS
---------- ------------------------------
         1 after first update
         2 after first update

----------CASE2
We now see how we can use our workspace to go to previous point in time to query data.

SQL> var date1 varchar2(30);
SQL> exec select to_char(sysdate, 'dd-month-yyyy hh24-mi-ss') into :date1 from dual;
PL/SQL procedure successfully completed.
SQL> insert into table1 values (4,'fourth value');
1 row created.
SQL> insert into table1 values (5,'fifth value');
1 row created.
SQL> insert into table1 values (6,'sixth value');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from table1;
    T1_ID1 T1_DETAILS
---------- ------------------------------
         1 after first update
         2 after first update
         4 fourth value
         5 fifth value
         6 sixth value
SQL> exec dbms_wm.gotoDate(to_date(:date1,'dd-month-yyyy hh24-mi-ss'));
PL/SQL procedure successfully completed.
SQL> select * from table1;
    T1_ID1 T1_DETAILS
---------- ------------------------------
         1 after first update
         2 after first update   

We disable the versioning and we see that all other objects have been dropped.

SQL> exec dbms_wm.disableversioning('TABLE1');
PL/SQL procedure successfully completed.
SQL> select count(*) from user_objects;
  COUNT(*)
----------
         2

----------CASE3
We are now considering the case of versioning some tables which are in a parent-child relation.
For version enabling such tables it must be followed this order: first version enable the child tables and then the parent tables.
For version disabling the reverse order must be followed.

SQL> create table t1 (id1 number primary key, detail1 varchar2(10));
Table created.
SQL> create table t2 (id2 number primary key, detail2 varchar2(10), id1 references t1(id1));
Table created.
SQL> create table t3 (id3 number primary key, detail3 varchar2(10), id2 references t2(id2));
Table created.
SQL> exec dbms_wm.enableversioning('T2');
BEGIN dbms_wm.enableversioning('T2'); END;
*
ERROR at line 1:
ORA-20100: 'HB.T2' is both parent and child tables of referential integrity constraints
ORA-06512: at "WMSYS.LT", line 9175
ORA-06512: at line 1

SQL> exec dbms_wm.enableversioning('T1');
BEGIN dbms_wm.enableversioning('T1'); END;
*
ERROR at line 1:
ORA-20101: child table must be version enabled
ORA-06512: at "WMSYS.LT", line 9175
ORA-06512: at line 1

SQL> exec dbms_wm.enableversioning('T3');
PL/SQL procedure successfully completed.
SQL> exec dbms_wm.enableversioning('T2');
PL/SQL procedure successfully completed.
SQL> exec dbms_wm.enableversioning('T1');
PL/SQL procedure successfully completed.
SQL> exec dbms_wm.disableversioning('T3');
BEGIN dbms_wm.disableversioning('T3'); END;
*
ERROR at line 1:
ORA-20104: cannot version disable this table
ORA-06512: at "WMSYS.LT", line 9355
ORA-06512: at line 1

Now we do the cleanup:

SQL> exec dbms_wm.disableversioning('T1');
PL/SQL procedure successfully completed. 
SQL> exec dbms_wm.disableversioning('T2');
PL/SQL procedure successfully completed.
SQL> exec dbms_wm.disableversioning('T3');
PL/SQL procedure successfully completed.
SQL> drop table t3;
 
Table dropped.
SQL> drop table t2;
Table dropped.
SQL> drop table t1;
Table dropped.
        
        
References:
Oracle Workspace Manager - an Example (9i,10g,11g) [ID 181465.1]
Introduction to Workspace Manager [ID 156574.1]
ORA-20100, ORA-20101 Trying to Version Enable a Table [ID 263431.1]
Frequently Asked Questions for Oracle Workspace Manager (OWM) [ID 156963.1]