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]
No comments:
Post a Comment