Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 468
Dmitry Khalatov Avatar asked Jan 12 '09 11:01

Dmitry Khalatov


People also ask

Does Oracle reuse Rowid?

It can be reused: If you delete a row, then Oracle may reassign its rowid to a new row inserted later. Save this answer.

Is Oracle Rowid sequential?

They are unique but they are not sequential. Each SPU is allocated a block of numbers and assigns row id's locally. These row numbers are not necessarily sequential within a table. Usually, the initial rowid value is 100,000.

Which is true for Rowid datatype?

A ROWID data type stores information related to the disk location of table rows. They also uniquely identify the rows in your table. The ROWID data type is stored as a hexadecimal. Therefore the hexadecimal string represents the unique address of a row in its table.


2 Answers

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.

like image 161
WW. Avatar answered Sep 28 '22 05:09

WW.


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

like image 20
Thilo Avatar answered Sep 28 '22 05:09

Thilo