Top 60 Oracle Blogs

Recent comments

Transportable tablespaces and ROWID uniqueness

I recently saw a fellow OakTable member mentioning a section in Oracle documentation where it’s said that:

“When a database contains tablespaces that have been plugged in (from other databases), the ROWIDs in that database are no longer unique. A ROWID is guaranteed unique only within a table.”

It’s a well known fact that the old Oracle7 style restricted rowids (which contained only File#, block# and row#) may not be unique in Oracle8+ databases which can have 1022 datafiles per tablespace not per database as previously. That’s why the 10-byte extended rowids were introduced, which also included the data object ID of a segment inside the rowid.

So if you have a global (partitioned) index on a partitioned table, the new extended rowids are used in it. With help of data dictionary cache this allows Oracle to quickly figure out in which tablespace the referenced segment resides and then use old fashioned relative-file#, block# and row# lookup on it.

Note that with local indexes and non-partitioned tables the rowids stored in indexes are old 6-byte restricted rowids. They have 4 bytes for data block address consisting of 10bits for file# and 22bits for block#. The other 2 bytes specify the row# in block.

The 10-byte extended rowids used to be unique within a database, until the transportable tablespaces came into play. Why is that – it’s because how transportable tablespaces work.

The idea behind TTS’es is that one should be able to copy (large) tablespaces around at file level, very fast, without much pre- or post-processing. So when we copy an 1TB TTS around, we don’t want to start scanning through it after plug-in to make ROWIDs in indexes and chained row forward pointers to somehow “make them right”. Thus, when plugging in a TTS, we need to keep all low-level row addressing structures untouched. This means that file#, block# and row# parts in index leaves and chained row pointers need to stay as they are. This means that DBAs (data block addresses in ASSM blocks need to stay as they are).

Also, as every segment data block holds a data object ID in it (which is also part of extended rowid as I mentioned), we can’t change this either, as otherwise we would need to scan through all tables/indexes in that tablespace again.

Thus, if all components of extended ROWID of an imported TTS need to stay as they are, there is theoretical and practical chance of ROWID collision within a database (note that I said within a database, not table).

A test case for demonstrating this is quite simple: