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.

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.

Answer 2

+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.

Answer 3

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.