Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
I don't think we need a ora_rowscn tag (it is too specific) or an update tag (it's not necessary for a site focused on advanced database Q&A) –  Jack Douglas Aug 26 '11 at 7:52

3 Answers 3

up vote 8 down vote accepted

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.

share|improve this answer
    
Thanks for the link to a good article. Unfortunately, we've already tried auditing, but the DBAs won't turn that on and are adamant about it for some reason. As far as the false positive you mention, this is okay because we're not looking to see if one row has been updated, but any row in the table, so I think ora_rowscn will work for us. –  oscilatingcretin Aug 26 '11 at 12:22
    
I marked yours as the answer because your article gave me exactly what I need to know. Good job, sir. –  oscilatingcretin Aug 26 '11 at 12:49
    
+1 as your answer is more to the point than mine. –  Leigh Riffel Aug 26 '11 at 14:27

As @Leigh Riffel noted, a select max(ora_rowscn) will result in a full table scan. An alternative is to have a timestamp column (we will call sys_ts for this example) which is populated to the time of statement execution (which is not the same as statement commitment, which is when the scn is generated/populated). An index on the sys_ts column will allow you to look at the most recent x (say 25) rows to find the max ora_rowscn only from those rows.

The most straight forward way to do this would be to use the rownum to limit results as described in this[1] ask tom article:

select *
from (select sys_ts, ora_rowscn
from table order by SYS_TS desc) where rownum < 25;

Unfortunately, that also results in a full table scan (at least in oracle 11.2.0.3). This has been reported to Oracle, but determined not to be a defect (bug 17347125).

This requires a bit more work to achieve effectively the same result:

select b.sys_ts,b.ora_rowscn from
    (select rid from 
        (select rowid as rid from table order by sys_ts desc) 
    where rownum <= 25) a, table b
where a.rid = b.rowid;

[1] - http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

share|improve this answer
    
Oracle is now investigating a new bug related to the full table scan (18318631). So the simple limited query may end up not causing a fts in the future. –  Brett Okken Feb 28 at 14:33

Getting the max(ora_rowscn) will require a full table scan each time you do it. It may be faster just to refresh the entire cache each time.

It sounds like you need a way to notify the other service that a change took place and what the change was. You could maintain a log table with a column that indicates which system needs to consume the change. The column could have two function based indexes one for each service so that each index contains only the entries that need to be consumed. Then as they are consumed they can make the value NULL to remove it from the index.

Or you could just use Oracle's Advanced Queuing.

share|improve this answer
    
I thought of something similar. If a record is inserted, updated, or deleted, it writes a date to a field in a table then stores that date in a static variable in the application. The next time it goes to retrieve the collection, it checks if the date in the db is new than the one stored in the static variable. If it is, it gets the collection from the db again. Otherwise, it uses the cached collection. I don't want to get the collection every time because it can be up to 500 records. –  oscilatingcretin Aug 26 '11 at 1:06
1  
Depending on how frequently your services change the data, you may run into concurrency problems with each service having to wait for the other to update the date before it can do so itself. Perhaps more importantly each could check the date and finding it unchanged make a change assuming each had the most recent version. If they are updating the same records you could run into a deadlock. –  Leigh Riffel Aug 26 '11 at 1:37
    
I just did a max(ora_rowscn) on a table with 73,985 records and it took 0.078 seconds, so I think this will work out. After all, the alternative is to retrieve 213 records and then fill the .NET entity objects every time. Also, I don't think there will be a concurrency issue, but I don't want to convolute the discussion as to why I don't think so. In a nutshell, though, as long as both services cache the current date and time locally before writing that value to the database, I think it will work. –  oscilatingcretin Aug 26 '11 at 12:49
1  
I'm glad you've considered the concurrency issue. On the max(ora_rowscn) timing side there is more to consider than the raw lookup time. By doing a full table scan the database must pull the data blocks into memory. For your timing this may already have been done giving you the fast response. Even if it takes this long from disk that is still data put in the buffer cache that could be used for other data. The date/time route would pull one record into memory rather than 73,985. Just a though, hope that helps. –  Leigh Riffel Aug 26 '11 at 14:26
    
Interesting info for an Oracle noob such as myself. So let's say I am dealing with a table of 300 records and 15 columns. If I were return select * from suppliers to a cursor, does all that data get cached in the same buffer? –  oscilatingcretin Aug 26 '11 at 14:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.