Are you using 9i/10G and still implement optimistic locking with your own column rather than through Oracle's pseudo column? So am I, but I couldn't really explain why. My main (defensive) argument would be that the system was built long before 9i. Still, it would make sense for us to change it. Let's look at a, hopefully, quick example.
Let's first create a user with two tables and add the same data to both tables.
create user rowscn identified by rowscn;
alter user rowscn default tablespace users;
alter user rowscn quota unlimited on users;
grant resource, create session to rowscn;
create table t1 (id number, total number);
insert into t1 (id, total) values(1,100);
insert into t1 (id, total) values(2,200);
insert into t1 (id, total) values(3,300);
create table t2 (id number, total number) rowdependencies;
insert into t2 (id, total) values(1,100);
insert into t2 (id, total) values(2,200);
insert into t2 (id, total) values(3,300);
Two tables with just one "small" difference. We'll soon see see the difference it makes. To see how this works, we'll use a simple update of these three rows.
update t1 set total = total + 1 where id = 1;
update t1 set total = total + 1 where id = 2;
update t1 set total = total + 1 where id = 3;
Each row was updated in a different transaction as we committed between each update. If we now look at the pseudo column, ORA_ROWSCN, we will see something like:
select id, total, ora_rowscn from t1;
ID TOTAL ORA_ROWSCN
Posted in Uncategorized
It's time to start a blog and write down some ideas, findings, and general commentary about development with the Oracle database. This blog will be a place where I write about things related to Oracle development and performance that interests me.
I have been a DBA for longer than I really want to admit as that leads to the conclusion that I'm a middle aged man today. When I started in this industry, I used to feel that the middle aged men didn't know or understand anything that had been introduced in the last ten years. I'm sure it is the case with me in some areas, but I hope my interest for databases will make the content here usable for current versions of Oracle.
My interest and passion is using Oracle database technologies to build database driven applications that perform well. That is, I'm not interested in Oracle to build applications for the database, but rather to use the database to make better and faster applications.
Initially much of what you find here will be based on Tom Kyte's last book Expert Oracle Database Architecture. If you want to read about Oracle, then this is a great book. One of the most well written books I've come across and Tom makes even really complex material relatively easy to understand without reducing the complexity for the areas you need to fully understand to build better applications with Oracle. I'm not planning to just take Tom's ideas and turn his book into my blog, instead I will write about things I've thought of as a result of reading his book.
Writing about interesting side effects or proving just why some concepts are really important is what I want to do on this blog. Just rewriting the concepts guide or the performance manual serves little purpose unless one is interested in writing a book. Sure, Oracle's documentation is often in need of a guide to explain why it is important or how a technique should be used. To me, that is still not a blog I'd read. For that I buy a book or search for a site that just expands a little on the text in Oracle's official documentation.
Posted in Uncategorized