What Can Cause an Oracle Rowid to Change

What can cause an Oracle ROWID to change?

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.

Is it safe to use ROWID to locate a Row/Record in Oracle?

"From Oracle 8 the ROWID format and size changed from 8 to 10 bytes. Note that ROWID's will change when you reorganize or export/import a table. In case of a partitioned table, it also changes if the row migrates from a partition to another one during an UPDATE."

http://www.orafaq.com/wiki/ROWID

I'd say no. This could be safe if for instance the application stores ROWID temporarily(say generating a list of select-able items, each identified with ROWID, but the list is routinely regenerated and not stored). But if ROWID is used in any persistent way it's not safe.

Will rowid keep unchanged if I export from one oracle and then import to another oracle

No, the row IDs will almost certainly change. Even within the same database, from the docs:

If you delete and reinsert a row with the Import and Export utilities, for example, then its rowid may change.

The row ID represents the location of the row within a block, within a data file, within a tablespace. (That documentation explains that more.) Even if the target database has the same tablespaces and data files, the import will load data into files and blocks as efficiently as it can, and will not make any attempt to preserve old row IDs - which it won't know anyway as they are not part of the exported data. Even if it could try, that would involve writing each row to a specific place on disk, which would slow things down quite a bit, and existing data in the target DB might already be using the same row ID.

ROWID is a pseudocolumn, not part of the the actual row, and it would be meaningless to include it in the exported data.

Although you can use the ROWID pseudocolumn in the SELECT and WHERE clause of a query, these pseudocolumn values are not actually stored in the database.

It isn't even necessarily unique.

Also, you shouldn't really be using it directly, except possibly within a single query/statement (here is one use) or maybe procedure, as they can change even within an existing database if Oracle decides it needs to reorganize things. That's partly why the documentation also says:

You should not use ROWID as the primary key of a table.

Will rowid be reused?

The RowID are physical location of the data stored on the disk. They are unique for a table. As such they can repeat. If the new record gets stored in the same place (deleting the older one), then rowids would be same (reused).

See this article for details on what rowid consists of in oracle
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns008.htm

Join tables on Oracle ROWID

Now, ROWID is encoded.

DOn't you use standard oracle type ROWID? If - yes, you don't need to convert it.
For example, your table B has column TAB_A_ROWID which contains rowid of related record in table A. So you can easily join them:

select *
from A join B on B.TAB_A_ROWID = A.rowid

Btw, in you code I see "ta.rowid = tb.rowid"

UPDATE tablea ta
  SET (colx) = (SELECT colx FROM tableb tb WHERE ta.rowid = tb.rowid)

but rowid is pseudocolumn, You can't add column rowid. so you specified both of them - real rowids, not from own column.

How does order by row id work in Oracle?

Why you see is only a representation used for display purposes.

The actual rowid contains binary information about the data block, the row in the block, the file where the block is located and the internal object id of the table (See the manual for details)

When you use order by rowid Oracle sorts the rows based on that (internal) information, not based on the "string representation".

If you change your query to:

select rowid, 
       dbms_rowid.rowid_relative_fno(rowid) as rel_fno,
       dbms_rowid.rowid_row_number(rowid) as row_num,
       dbms_rowid.rowid_block_number(rowid) as block_num, 
       dbms_rowid.rowid_object(rowid)
from points 
order by rowid

You will most probably see the logic behind the ordering of the rownumber.

Note that the value for dbms_rowid.rowid_object() will always be the same. And if you only have two rows in your table, both will most probably also have the same value for rowid_block_number()

---

Related Topics