Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Rhys.

Asked: June 06, 2006 - 7:20 pm UTC

Last updated: June 07, 2006 - 3:24 pm UTC

Version: 10.2.0

Viewed 10K+ times! This question is

You Asked

Tom,

Under what conditions will the rowid of a given record change? e.g. if in a partitioned table, I UPDATE a record such that it moves to another partition, does this imply that the rowid will change too?

Are there other situations where the rowid can change based on an UPDATE?

Best regards,


Rhys


and Tom said...

For this to happen, the table must "allow for row movment"

and it will happen when:

a) you update a partition key and the row moves partitions

b) shrink a table (10g new feature), since rows move from the bottom to the "top" of the table in anticipation of re-drawing the high water mark (we have to change their rowids)

c) flashback a table (10g new feature), since the flashback table command really issues a DELETE+INSERT to put the data back the way it was.


Of course the UROWID of a row in an IOT can change if you modify the key as well. But it isn't a "true" rowid.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

reorg?

oraboy, June 07, 2006 - 12:46 pm UTC

Plus ,

alter table <<tablename>> move

would change the rowids, obviously!!


SQL> create table test1 (c1 number);

Table created.

SQL> insert into test1 select rownum from user_objects where rownum<4;

3 rows created.

SQL> select rowid,c1 from test1;

ROWID                      C1
------------------ ----------
AAAF6DAAKAAGwJ1AAA          1
AAAF6DAAKAAGwJ1AAB          2
AAAF6DAAKAAGwJ1AAC          3

SQL> alter table test1 move;

Table altered.

SQL> select rowid,c1 from test1;

ROWID                      C1
------------------ ----------
AAAF6EAAKAAGwKEAAA          1
AAAF6EAAKAAGwKEAAB          2
AAAF6EAAKAAGwKEAAC          3

SQL> 

 

Tom Kyte
June 07, 2006 - 3:24 pm UTC

doh, missed that one, yes....

(as would an online redefine obviously :)