How oracle rowid is generated internally?

Question

I want to know, does the ROWID in oracle get generated incrementally? If I try below query

select min(ROWID) from table

will I always get the ROWID of first inserted row in the table or I may end up getting a ROWID OF any random row also? It would be very helpful if someone could please throw light in this

Answer 1

The "minimum" rowid will probably not always provide the first inserted row from the table. To quote from the documentation:

After a rowid is assigned to a row piece, the rowid can change in special circumstances. For example, if row movement is enabled, then the rowid can change because of partition key updates, Flashback Table operations, shrink table operations, and so on. If row movement is disabled, then a rowid can change if the row is exported and imported using Oracle Database utilities.

The "and so on" indicates that there are many reasons which would cause a rowid to change. This can easily be demonstrated with a small example:

create table tmp_test ( a number );
insert into tmp_test values (1);
select rowid, a from tmp_test;

ROWID                       A
------------------ ----------
AABo3AAFvAAAda6AAA          1
alter table tmp_test move;
select rowid, a from tmp_test;

ROWID                       A
------------------ ----------
AABo3BAFvAAAdbjAAA          1

You'll notice that after an alter table operation the sole rowid has changed.

If the rowid can change and as Oracle does not explicitly guarantee that the "lowest" rowid will always be the first inserted row you should have another way of tracking this, if required. A timestamp or an incrementing sequence would be normal.

It must be a pretty unusual requirement to find the first inserted row without reference to any data. If this is something you're considering using I'd take another look at why you need to do this.