What can cause an Oracle ROWID to change?

Question

AFAIK ROWID in Oracle represents physical location of a record in appropriate datafile. In which cases ROWID of a record may change ?

The one known to me is UPDATE on partitioned table that "moves" the record to another partition.

Are there another cases ? Most of our DBs are Oracle 10.

Payson

Answer 1

As you have said, it occurs anytime the row is physically moved on disk, such as:

• Export/import of the table
• ALTER TABLE XXXX MOVE
• ALTER TABLE XXXX SHRINK SPACE
• FLASHBACK TABLE XXXX
• Splitting a partition
• Updating a value so that it moves to a new partition
• Combining two partitions

If is in an index organized table, then an update to the primary key would give you a different ROWID as well.

Lankester

Also, UPDATEing (a previously NULL value, say) may make the row large enough that it can no longer be accommodated on its current page. In an application where large numbers of UPDATEs occur, this would probably be fairly common. – Brant

Mike, that would lead to row chaining. The row's apparent ROWID doesn't change, but in the record you get a pointer to another block where the full record is placed. – Derzon

It would be row migration, which happens when the row grows to be too big for the current block but is big enough to be contained in a single block. Chaining occurs when the row is too big for a single block. But you're right that a pointer to a migrated row is left in the original block. – Nominee

Answer 2

Another +1 to WW, but just to add a little extra...

If the driving question is whether you can store ROWIDs for later use, I would say "don't do it".

You are fine to use ROWIDs within a transaction - for example collecting a set of ROWIDs on which to carry out a subsequent operations - but you should never store the ROWIDs in a table and assume they're going to be ok to use at a later date.

Aver

Going to use Oracle database change notification feature, the only information that Oracle jdbc driver expose when row is changed is ROWID. I believe, relying on ROWID is stable solution. If you are not going alter your table between transactions. – Recto

Answer 3

+1 @WW

As an aside:

ROWID for index organized tables are different (they are called UROWID, I believe), because the physical location of the row can change during updates to the table (when tree nodes split or are joined).

In order to make indexing still possible, the UROWID includes the "logical id" (the primary key), and the "likely physical id" (a regular ROWID), the latter of which may be expired.

Kynewulf