I won't talk much about features of Workspace Manager cause guide from documentation is very detailed and worth reading. In this blog post I'll concentrate on simple example how to use Workspace Manager to keep a history of changes to data.
Developers often use custom triggers to keep a history of changes in log table but using Workspace Manager for that purpose is even simpler because you don't have to write custom trigger every time you want to log changes for various tables.
First to create test table with primary key:
system@TESTDB> grant select on scott.emp to msutic; Grant succeeded. msutic@TESTDB> create table emp as 2 select * from scott.emp; Table created. msutic@TESTDB> alter table emp add constraint emp_pk 2 primary key (empno); Table altered.
Now I will grant execution on package dbms_wm to user msutic:
system@TESTDB> grant execute on dbms_wm to msutic; Grant succeeded.
Now to enable versioning on created table EMP. I'll use EnableVersioning procedure to create necessary structures for supporting multiple versions of rows.
msutic@TESTDB> begin
2 dbms_wm.EnableVersioning('EMP','VIEW_WO_OVERWRITE');
3 end;
4 /
PL/SQL procedure successfully completed.In this example I've used 'VIEW_WO_OVERWRITE' for history opinion to track changes without overwriting. A view named EMP_HIST will be created to contain history information, and it will show all row modifications of EMP table.
Simple query:
msutic@TESTDB> set lines 200 msutic@TESTDB> col empno for 9999 msutic@TESTDB> col ename for a10 msutic@TESTDB> col sal for 99999 msutic@TESTDB> col WM_CREATETIME for a35 msutic@TESTDB> col WM_RETIRETIME for a35 msutic@TESTDB> msutic@TESTDB> msutic@TESTDB> select empno,ename,sal,WM_OPTYPE, 2 WM_CREATETIME,WM_RETIRETIME 3 from emp_hist; EMPNO ENAME SAL WM WM_CREATETIME WM_RETIRETIME ----- ---------- ------ -- ----------------------------------- ----------------------------------- 7369 SMITH 800 I 07.01.09 21:07:06.043323 +01:00 7499 ALLEN 1600 I 07.01.09 21:07:06.043323 +01:00 7521 WARD 1250 I 07.01.09 21:07:06.043323 +01:00 7566 JONES 2975 I 07.01.09 21:07:06.043323 +01:00 7654 MARTIN 1250 I 07.01.09 21:07:06.043323 +01:00 7698 BLAKE 2850 I 07.01.09 21:07:06.043323 +01:00 7782 CLARK 2450 I 07.01.09 21:07:06.043323 +01:00 7788 SCOTT 3000 I 07.01.09 21:07:06.043323 +01:00 7839 KING 5000 I 07.01.09 21:07:06.043323 +01:00 7844 TURNER 1500 I 07.01.09 21:07:06.043323 +01:00 7876 ADAMS 1100 I 07.01.09 21:07:06.043323 +01:00 7900 JAMES 950 I 07.01.09 21:07:06.043323 +01:00 7902 FORD 3000 I 07.01.09 21:07:06.043323 +01:00 7934 MILLER 1300 I 07.01.09 21:07:06.043323 +01:00 14 rows selected.
Now I'll make some changes to table.
msutic@TESTDB> update emp set sal=40000 where empno=7369; 1 row updated. msutic@TESTDB> commit; Commit complete. msutic@TESTDB> select empno,ename,sal,WM_OPTYPE, 2 WM_CREATETIME,WM_RETIRETIME 3 from emp_hist; EMPNO ENAME SAL WM WM_CREATETIME WM_RETIRETIME ----- ---------- ------ -- ----------------------------------- ----------------------------------- 7369 SMITH 800 I 07.01.09 21:07:06.043323 +01:00 07.01.09 21:15:05.127574 +01:00 7369 SMITH 40000 U 07.01.09 21:15:05.127574 +01:00 7499 ALLEN 1600 I 07.01.09 21:07:06.043323 +01:00 7521 WARD 1250 I 07.01.09 21:07:06.043323 +01:00 7566 JONES 2975 I 07.01.09 21:07:06.043323 +01:00 7654 MARTIN 1250 I 07.01.09 21:07:06.043323 +01:00 7698 BLAKE 2850 I 07.01.09 21:07:06.043323 +01:00 7782 CLARK 2450 I 07.01.09 21:07:06.043323 +01:00 7788 SCOTT 3000 I 07.01.09 21:07:06.043323 +01:00 7839 KING 5000 I 07.01.09 21:07:06.043323 +01:00 7844 TURNER 1500 I 07.01.09 21:07:06.043323 +01:00 7876 ADAMS 1100 I 07.01.09 21:07:06.043323 +01:00 7900 JAMES 950 I 07.01.09 21:07:06.043323 +01:00 7902 FORD 3000 I 07.01.09 21:07:06.043323 +01:00 7934 MILLER 1300 I 07.01.09 21:07:06.043323 +01:00 15 rows selected.
Querying EMP_HIST you can easily track latest change to EMP table. I could see what operation was made on row and modifications.
To return in time before modification:
msutic@TESTDB> begin
2 dbms_wm.gotoDate(to_date('07.01.09 21:10:00','dd.mm.yyyy hh24:mi:ss'));
3 end;
4 /
PL/SQL procedure successfully completed.
msutic@TESTDB> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ---------- ------------------- ------ ---------- ----------
7369 SMITH CLERK 7902 17.12.1980 00:00:00 800 20
7499 ALLEN SALESMAN 7698 20.02.1981 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 22.02.1981 00:00:00 1250 500 30
7566 JONES MANAGER 7839 02.04.1981 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 28.09.1981 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 01.05.1981 00:00:00 2850 30
7782 CLARK MANAGER 7839 09.06.1981 00:00:00 2450 10
7788 SCOTT ANALYST 7566 19.04.1987 00:00:00 3000 20
7839 KING PRESIDENT 17.11.1981 00:00:00 5000 10
7844 TURNER SALESMAN 7698 08.09.1981 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 23.05.1987 00:00:00 1100 20
7900 JAMES CLERK 7698 03.12.1981 00:00:00 950 30
7902 FORD ANALYST 7566 03.12.1981 00:00:00 3000 20
7934 MILLER CLERK 7782 23.01.1982 00:00:00 1300 10
14 rows selected.For another test I'll create workspace and go to that workspace and make data changes.
msutic@TESTDB> begin
2 dbms_wm.createWorkspace('EMP_UPDATES');
3 end;
4 /
PL/SQL procedure successfully completed.
msutic@TESTDB> begin
2 dbms_wm.gotoWorkspace('EMP_UPDATES');
3 end;
4 /
PL/SQL procedure successfully completed.
msutic@TESTDB> update emp set sal = 10000
2 where sal<1500;
5 rows updated.
msutic@TESTDB> update emp set ename='ANDY'
2 where empno=7902;
1 row updated.
msutic@TESTDB> delete from emp
2 where ename='KING';
1 row deleted.
msutic@TESTDB> commit;
Commit complete.
msutic@TESTDB> select empno,ename,sal
2 from emp;
EMPNO ENAME SAL
----- ---------- ------
7499 ALLEN 1600
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7844 TURNER 1500
7369 SMITH 40000
7521 WARD 10000
7654 MARTIN 10000
7876 ADAMS 10000
7900 JAMES 10000
7934 MILLER 10000
7902 ANDY 3000
13 rows selected.
I'll create savepoint on that changes and make more changes.
msutic@TESTDB> begin
2 dbms_wm.CreateSavepoint('EMP_UPDATES','EMP_UPDATES_SP1');
3 end;
4 /
PL/SQL procedure successfully completed.
msutic@TESTDB> delete from emp
2 where sal=10000;
5 rows deleted.
msutic@TESTDB> commit;
Commit complete.
msutic@TESTDB> select empno,ename,sal
2 from emp;
EMPNO ENAME SAL
----- ---------- ------
7499 ALLEN 1600
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7844 TURNER 1500
7369 SMITH 40000
7902 ANDY 3000
8 rows selected.To undo last changes:
msutic@TESTDB> begin
2 dbms_wm.RollbackToSp('EMP_UPDATES','EMP_UPDATES_SP1');
3 end;
4 /
PL/SQL procedure successfully completed.
msutic@TESTDB> select empno,ename,sal
2 from emp;
EMPNO ENAME SAL
----- ---------- ------
7499 ALLEN 1600
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7844 TURNER 1500
7369 SMITH 40000
7521 WARD 10000
7654 MARTIN 10000
7876 ADAMS 10000
7900 JAMES 10000
7934 MILLER 10000
7902 ANDY 3000
13 rows selected.To merge changes into LIVE (production) workspace and remove EMP_UPDATES workspace:
msutic@TESTDB> begin
2 dbms_wm.gotoWorkspace('LIVE');
3 end;
4 /
PL/SQL procedure successfully completed.
msutic@TESTDB> begin
2 dbms_wm.MergeWorkspace('EMP_UPDATES',remove_workspace=>true);
3 end;
4 /
PL/SQL procedure successfully completed.Disable versioning and clean up:
msutic@TESTDB> begin
2 dbms_wm.DisableVersioning('EMP');
3 end;
4 /
PL/SQL procedure successfully completed.
msutic@TESTDB> drop table EMP purge;
Table dropped.
Oracle9i introduced the concept of a database workspace manager. A workspace is an environment for a long-term transaction that allows versioning of table rows. A workspace can be shared among multiple users. The concept of workspace manager involves a series of short transactions and multiple data versions to implement a complete long-transaction event that maintains atomicity and concurrency.
ReplyDelete