Skip to Main Content

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

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)

We're not taking comments currently, so please try again later if you want to add 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 :)

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.