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

min(ROWID) will always return you the first row of the table.

For each row in the database, the ROWID pseudocolumn returns the address of the row. Oracle Database rowid values contain information necessary to locate a row:

1. The data object number of the object
2. The data block in the datafile in which the row resides
3. The position of the row in the data block (first row is 0)
4. The datafile in which the row resides (first file is 1). The file number is relative to the tablespace.

Usually, a rowid value uniquely identifies a row in the database. However, rows in different tables that are stored together in the same cluster can have the same rowid.

Go to Official site Here for more detail.

Answer 2

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.