Oracle 10g: Properly using ora_rowscn to detect table row changes (ie, inserts, updates, deletes)

deleteinsertoracle-10gupdate

Some research into checking when a table's records were last updated, modified, or deleted has lead me to the pseudo column known as ora_rowscn.

First, I do this:

select max(ora_rowscn) from tablename;

I take note of the number. Then I do an insert, update, and a delete, check that max value before and after each. It appears to increment for each type of change.

If you're wondering why I am doing this, we cache a list of entities in our C# windows service. This service runs on two load-balanced servers, so there's a separate instance of each running. When an update occurs on server A, server B needs to know about it. What I want to do is cache max(ora_rowscn) into a variable. Every time our application goes to insert, update, or delete a record, it will get a new max from the database. If the value is different then it obviously knows it needs to go get a new list from the database.

So my actual question is this: Are there any other snags I should be aware of that might result in an insert, update, or deletion of a record not incrementing this value?

Edit: Can someone add ora_rowscn as a tag?

Best Answer

Are there any other snags I should be aware of that might result in an insert, update, or deletion of a record not incrementing this value?

ora_rowscn is always incremented when a row changes - but in a default configuration it can also be incremented when a row does not change

If you need to check the whole table for udates, one method is to use auditing. On the other hand if you only need to check the row you are trying to update for conflicts, ora_rowscn with rowdependencies is ideal.

Related Question