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? Also, why is
update not a tag in the dba section???