Oracle on Linux

I came across the following article "Introduction to Oracle on Linux" recently and it had the following excellent point.

• Install Oracle on a version of Linux you are likely to use in your professional life

So what version of Linux is that? Well Oracle is certified on Red Hat Enterprise Server or Suse Enterprise Server. Now the unfortunate thing about these operating systems is that they are not free. However there is now several clones that are free including CentOS.

When I visited the CentOS website I found out that CentOS-4 is featured in the April 2006 edition of Linux Magazine (Issue 65). So I ordered the magazine and when it arrives I am going to install Linux and then Oracle 10g. I'll let you know how it all goes.

Select allows locks

I was reading the March/April edition of Oracle magazine and found Tom Kyte's article on how select allows locks very interesting. Although Tom points out some reasons for allowing this, I would prefer that you could not lock tables with only the select privilege.

One reason given was that if you had to delete from table P and P is the parent of C. And you have read/write access to P and only read access to C. When you attempt to delete from P, Oracle will try and lock a bit of C (if indexes foreign keys exist) or all of C (if there is no index).

In this scenario, I would prefer the delete to fail unless you had read/write access to both P and C rather than allowing anybody with select access to be able to lock the entire table.

However this is how Oracle works and it is definately worth knowing.

Multiversioning

Today I came across one of those situations where a developer needs to be aware of Oracle's multiversion concurrency architecture. A table was created as a lookup table to generate surrogate keys for dimension tables in a data warehouse. Whenever a new row for a dimension was to be loaded, a piece of code was called to find the current maximum key value and add 1 to it. The problem with this is that due to Oracle's multiversioning, if two processes execute this query at the same time, they will both get the same value for the next key. This is obviously not what we want in this situation. The solution is to serialize access to the table by executing a select for update statement prior to retrieving the next key. Or a better solution is to use sequences and bypass the need for serialization at all.

Here is a simple shell script that I put together that illustrates this happening. It executes two sqlplus sessions concurrently in the background, and both sessions select from the lookup table and get the same key.

#!/bin/sh
sqlplus username/password <<endsql
drop table key_lookup;
create table key_lookup(table_name varchar2(30),key number);
insert into key_lookup(table_name, key) values ('DIM_CUSTOMER', 5);
exit
endsq

lfor i in 1 2
do
FILE=`echo $i`
(sqlplus username/password <<endsql
begin for x in ( select nvl(max(key), 0) + 1 as new_key
from key_lookup
where table_name = 'DIM_CUSTOMER' )
loop
insert into key_lookup (table_name, key) values ('DIM_CUSTOMER', x.new_key);
end loop;
end;
/
exit
endsql ) > $FILE 2>&1 &
done

echo Sleep for 10 seconds to wait for process to finish
sleep 10
sqlplus username/password <<endsql
spool lookup.lst
select table_name, key from key_lookup order by table_name, key;
spool off
exit
endsql

The end result is:
ora92> select table_name, key from key_lookup order by table_name, key;
TABLE_NAME KEY
------------------------------ ----------
DIM_CUSTOMER 5
DIM_CUSTOMER 6
DIM_CUSTOMER 6