Search code examples
sqldatabaseoracle-databaserowid

What can cause an Oracle ROWID to change?

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.

Solution

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