What can cause an Oracle ROWID to change?

sql database oracle rowid
Dmitry Khalatov picture Dmitry Khalatov · Jan 12, 2009 · Viewed 14.2k times · Source

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

WW. picture WW. · Jan 12, 2009

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.

Related questions

How do I list all the columns in a table?

For the various popular database systems, how do you list all the columns in a table?

Read More
How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More
How can I get column names from a table in Oracle?

I need to query the database to get the column names, not to be confused with data in the table. For example, if I have a table named EVENT_LOG that contains eventID, eventType, eventDesc, and eventTime, then I would …

Read More