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.
