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:

SQL> connect system/oracle
Connected.

SQL>
SQL> create tablespace t1 datafile 'c:\tmp\t1.dbf' size 10m;

Tablespace created.

SQL> create table mytab tablespace t1 as select * from dual;

Table created.

I have created a tablespace T1 with table MYTAB and now I'll clone it with TTS

SQL> alter tablespace t1 read only;

Tablespace altered.

Let's make a backup copy of my tablespace. This will be plugged back in to the same database

SQL> host copy c:\tmp\t1.dbf c:\tmp\t2.dbf
        1 file(s) copied.

SQL> create directory ttsdir as 'c:\tmp';

Directory created.

SQL> host expdp system/oracle dumpfile=tts.dmp directory=ttsdir transport_tablespaces=t1

Export: Release 10.2.0.1.0 - Production on Tuesday, 21 October, 2008 3:24:26

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** dumpfile=tts.dmp directory=ttsdir transport_tablespaces=t1
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  C:\TMP\TTS.DMP
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 03:24:44

Now I'll rename old T1 tablespace so that I can easily plug the exported TTS back (which is using the backup copy datafile I created above)

SQL> alter tablespace t1 rename to old_t1;

Tablespace altered.

Let's create another username and make impdp map the objects in it to the new user

SQL> grant create session, create table, unlimited tablespace to tanel identified by tmp;

Grant succeeded.

SQL> host impdp system/oracle dumpfile=tts.dmp directory=ttsdir transport_datafiles=c:\tmp\t2.dbf remap_schema=system:tanel

Import: Release 10.2.0.1.0 - Production on Tuesday, 21 October, 2008 3:29:21

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** dumpfile=tts.dmp directory=ttsdir transport_datafiles=c:\tmp\t2.dbf remap_schema=system:tanel
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 03:30:25

So, let's see what has happened:

SQL> select rowid from system.mytab;

ROWID
------------------
AAANDIAAGAAAAAMAAA

SQL> select rowid from tanel.mytab;

ROWID
------------------
AAANDIAAGAAAAAMAAA   -- does it look familiar? ;-)

So, here’s the evidence that an extended ROWID can be non-unique within a database when TTS is used.

This also means that you can have multiple segments with same data object ID in a TTS-ed database:

SQL> select owner, object_id, data_object_id
  2  from dba_objects
  3  where object_name = 'MYTAB';

OWNER                           OBJECT_ID DATA_OBJECT_ID
------------------------------ ---------- --------------
SYSTEM                              53448          53448
TANEL                               53511          53448 -- identical data object IDs!

Also, even though the absolute file number of the TTS-ed datafile is different in Oracle controlfile, the relative file number (which the data-block-addresses and ROWID’s utilize) is the same!

SQL> select tablespace_name, file_id, relative_fno from dba_data_files
  2  where tablespace_name like '%T1';

TABLESPACE_NAME         FILE_ID RELATIVE_FNO
-------------------- ---------- ------------
OLD_T1                        6            6
T1                            7            6

So, this is yet another example how smart and foreseeing the Oracle storage layer engineers have to be (as they introduced the absolute and relative file# concept and added data object ID into extended ROWID instead of just making the old file# component in DBA larger). That way they kept backward compatibility with old segments/tablespaces and have been able to add useful functionality like transportable tablespaces which have very low overhead for large data transfers.

Oh, what to do if you have such crap application in house and your business is relying on DB-wide uniqueness of ROWIDs for some weird reason – is there something you can do?

Yes, there is – even though it adds a “post processing” step which TTS was supposed to to eliminate: As ALTER TABLE MOVE creates a new segment for the moved table, the data object ID of that segment (and its newly created blocks) will be also different. Thus the ROWIDs for moved segment will be different as well.

SQL> alter tablespace t1 read write;

Tablespace altered.

SQL> select rowid from tanel.mytab;

ROWID
------------------
AAANDIAAGAAAAAMAAA

SQL> alter table tanel.mytab move;

Table altered.

SQL> select rowid from tanel.mytab;

ROWID
------------------
AAANEPAAGAAAAAUAAA  -- rowid is now different

SQL> select owner, object_id, data_object_id
  2  from dba_objects
  3  where object_name = 'MYTAB';

OWNER                           OBJECT_ID DATA_OBJECT_ID
------------------------------ ---------- --------------
SYSTEM                              53448          53448
TANEL                               53511          53519 -- the data object ID has changed!

SQL>

Finally, I don’t expect anyone to actually need or use this stuff I showed here, but this case is a great example for illustrating the crucial internal workings of storage addressing (DBAs and ROWIDs) in Oracle..

In other words, this was too cool to not be blogged :) That’s also the reason why I’m still awake at 4am, so I’m signing off.

This entry was posted in Oracle and tagged , . Bookmark the permalink.

17 Responses to Transportable tablespaces and ROWID uniqueness

  1. Dion Cho says:

    Very impressive.

  2. Arup Nanda says:

    Thanks for sharing this Tanel. Nothing, but, wow!

  3. Tom says:

    And after you move, don’t forget to rebuild indexes, right? :)

  4. Torben Holm says:

    Thaks for this clear and straight forward example

  5. binzhang says:

    what a powerful DBAs and ROWIDs.

  6. Stig says:

    Tried a little experiment just to see if this was watertight!

    The question:
    What if the same tablespace (with the same table renamed appropriately) was imported to the same database twice for being added to a partitioned table?
    This construct could actually be interesting in datawarehousing although it would give duplicat rowid’s within the very same table.

    Luckily Oracle thought on that as well, yeilding ORA-19728 “data object number conflict between table %s and partition %s in table %s”.
    So we are prevented from having more than one partition (=segment) with the same data_object_id.

    Just my 2c to demonstrate the difference between a data object and a table as a table may have more data objects (ie. segments) and to tie up a loose end that wasn’t tried.

    Thanks for the inspiration for this little additional test.

  7. tanelp says:

    @Tom: yep I sure would rebuild the indexes in alter table move case (which would make this post-processing step even longer)

    @Stig: Very cool! I never had thought of that (and I didn’t know about this conflict error message before). Thanks for telling that.

  8. Tanel, I was under the impression that ROWID column is computed and not stored. If the column is computed, then there is no need to recode it in the transported tablespace. The concepts manual clearly states that extended rowid is tablespace relative. That would explain the appearance of the same rowid in 2 different tablespaces.
    You did transport the tablespace into the same database. That would preserve physical characteristics enough to allow the same rowid.
    In other words, rowid pseudo-column is computed at the time of query and thus should couse no issue when the tablespace is transported.

  9. Stig says:

    Mladen,
    Yes, you’re right – the rowid IS computed. But the computation is a deterministic function as it would otherwise fail in a global index for a partitioned object, which can have multiple segments.
    The computation is merely a rather simple function to break the old 1022 files limitation, so any rowid in Tanels exampel is internally transformed into a physical rowid for the desired physical file and data object.
    Issues can still arise if you get tablespaces from several databases, which you comprise into a single partitioned table if the data object id (which is local to the tablespace and datafile) already exists as a segment in the partitioned table. This would give you the error I showed in my previous comment.
    In other words: You can get into trouble even without having duplicate rowid’s in the database. You just need 2 tablespaces each with an object with the same physical data object id for inclusion into the same partitioned table.

  10. tanelp says:

    Mladen,

    I think you misunderstood the point of my article.

    Oracle concepts guide apparently doesn’t state this uniqueness thing clearly enough – as I hadn’t noticed it :)

    Btw, rowids are actually stored – in indexes for example. As I mentioned, global indexes on partitioned tables store extended rowid. However as there’s no uniqueness across tablespaces, then more information is needed for rowid -> physical file block translation in addition to just rowid. And this info probably comes from dictionary cache (dc_segments).

    Also there are next-rowpiece pointers for chained and migrated rows which have only *relative* DBA + row# in them, so these need little logic as well which would translate the relative file# to an absolute file#.

    Transporting a tablespace to the same database was easiest and most straightforward test case – and it served its purpose, it proved a case how rowids can be non-unique.

    If something is possible, it’s only matter of time before it happens.

    So, to recap: the whole reason why we have rowids in Oracle is having secondary access paths like indexes – which DO have the rowids *stored* in them! If we didn’t store them, the index would be useless!

  11. Js says:

    Hi Tanel,

    I have been a silent reader of your blog.

    Today, I am curious to know that how much time you just spend to explore all these things apart your job.

    Or you job gives you all this exposure :)

    Actually, I am also interested but very much occupied with daily routine jobs at office and could not spend time to upgrade myself.

    But when I saw people like you … I am happy but want to know . how you manage ?

    Thanks -

  12. H Tanel,

    I heard you name from some many posters and referred your Blog. You are like “Jonathan Lewis”.

    I am very inspired by your posts.

    Really Great… !!

    Thanks

  13. Rajorshi says:

    Hi Tanel

    A very interesting revelation which even Oracle would be surprised to know.

    I have very trivial question for you. Is there a way to decode the blockid that a row resides in from its rowid by using x$ table or v$ view and without restoring to block dumps?

    thanks and regards
    Raj

  14. Tanel Poder says:

    Hi Raj,

    DBMS_ROWID package does this for you.

    You can run something like this:

    select
    dbms_rowid.ROWID_RELATIVE_FNO(rowid) rfile#
    , dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block#
    , dbms_rowid.ROWID_ROW_NUMBER(rowid) row#
    from
    mytable
    where
    id = 100
    /

  15. Rajorshi says:

    Thank you so much Tanel.

    It works for me. I wanted this to write a procedure that will give me the density of rows in a block for a table.
    So, that I can have a count of rows in a block before and after a purge job.

    regards Raj

  16. pafke says:

    Does someone know a way to duplicate a database and keeping the original rowid ?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>