Oracle 10g ORA_ROWSCN Pseudocolumn

Oracle 10g maintains a pseudocolumn the tracks the revision number of records. It’s called ORA_ROWSCN. (SCN stands for System Change Number.) There’s also a handy function, SCN_TO_TIMESTAMP, which will convert that revision number to a human-readable date and time.

But there are a few caveats:

• The revision numbers are tracked on a block level, meaning that even if you modify only one row, surrounding rows may also receive the new version number. You can turn on row-level tracking when you create the table, but there’s no way to do so after the fact.
• SCN_TO_TIMESTAMP will only be able to convert the SCN to a timestamp if the revision occurred within the last five days. Otherwise you get the lovely error: “ORA-08181: specified number is not a valid system change number”.

This article has a good description and two enlightening examples for those interested.

Alarm clock design

The last few days I’ve found myself waking up later than I plan, despite all my good intentions. And despite setting my alarm.

Aside from the fact that I really just ought to drag myself out of bed when I know I want to get up, I’m beginning to think my alarm clock (which is actually my cell phone) isn’t really fulfilling its purpose. We’ll put the blame on that device for the sake of argument.

What is an alarm clock for, anyway? It’s supposed to wake me up at some predetermined time (presumably a time I have selected). But in reality, all my alarm does at the moment is just beep at me. I invariably am too tired to get up when it goes off the first time, so I hit snooze, the button directly under the screen on the left. My phone faithfully plays it’s annoying little melody exactly five minutes later. And usually I hit snooze again.

In theory, I’ll eventually get tired of hitting snooze and just get out of bed. But I also don’t want to annoy my roommate with the incessant repetition of that lovely little tune. So I hit the “stop” button, which is directly under the right side of the screen. And I go back to sleep.

Somehow I seem to unconsciously know exactly when I will have to get up in order to have just enough time to shower, eat breakfast, and run to class. So a large percentage of the time I really do wake up “on time.” But the fact remains that the alarm clock is failing in its purpose.

First design flaw: The button to turn off the alarm is rather close to the button that just snoozes it. More than once I have accidentally hit the “stop” button, fully expecting the arousing tune to play again in five minutes. It doesn’t, and I disgruntedly scramble out of bed half an hour later and glare at the contraption. The point: Even if I’m sleepy, I shouldn’t be in danger of shutting down the alarm clock when all I really want to do is have it wait five minutes.

Second design flaw: Now this is the one that I don’t know how to fix. The purpose of the alarm clock is to get me out of bed when I had planned. All my phone does is beep at me and presume that I’ll take care of the rest. I have seen a few creative ideas (an R2D2 noisemaker, a helicopter, and an air-compressing bed-shaking wonder), but I don’t know that those are what I’m looking for.

I’ll keep thinking….