New design and best wishes...

First of all to wish Merry Christmas and Happy New Year to all readers of this blog. I wish you even more success in your business and family life in the year 2010.

About new design...

I had some free time to play with my blog so I decided to change design. Implementation of new design took me more time then I expected, because of my humble CSS/HTML knowledge, but at the and I am pleased with new look.

Thank you all for your participation and comments. I wish even more posts, visits and comments for this blog in next year :)

Read more →

DB Optimizer testing

Yesterday I've joined DBOptimizer 1.5 Beta Program and started to test application. DBOptimizer is great database performance tool introduced to us by database guru Kyle Hailey.

More information about application you can find here : DB Optimizer

I've already watched video and was impressed how simple is to diagnose database performance using this tool. That was the main reason for my appliance to beta program - to help find bugs and to discover application little more.



My first shots with application were not successful as I thought they would be because profiling was not working well on my system, but I'am sure that this will be fixed very soon.

I'll enjoy playing with application in next few weeks/months.

Read more →

Versioning on one or more user tables using Workspace Manager

Workspace Manager is powerful feature of Oracle Database introduced back in Oracle 9i. This feature could be very useful for managing current, proposed and historical versions of data. More information about feature you can find in Oracle documentation Oracle® Database Workspace Manager Developer's Guide.

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.

Read more →