Transportable Tablespaces (TTS) -- Common Questions and Issues

Master Note: (Doc ID 1166564.1)

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.2.0.2 - Release: 9.2 to 11.2
Oracle Server - Standard Edition - Version: 9.0.2.0 to 11.2.0.2   [Release: 9.0.1 to 11.2]
Information in this document applies to any platform.

Why use Transportable Tablespaces (TTS)?

• "Oracle transportable tablespaces are the fastest way for moving large volumes of data between two Oracle databases."

• "Using transportable tablespaces, Oracle data files (containing table data, indexes, and almost every other Oracle database object) can be directly transported from one database to another. Furthermore, like import and export, transportable tablespaces provide a mechanism for transporting metadata in addition to transporting data."

• "Transportable tablespaces have some limitations: source and target systems must be running Oracle8i (or higher), must use compatible character sets, and, prior to Oracle Database 10g, must run on the same operating system."

How to Validating Self Containing Property

TTS requires all the tablespaces, which we are moving must be self contained. This means that the segments within the migration tablespace set cannot have dependency to a segment in a tablespace out of the transportable tablespace set. This can be checked using the DBMS_TTS.TRANSPORT_SET_CHECK procedure.

SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('tbs', TRUE);

SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('tbs1, tbs2, tbs3', FALSE, TRUE);

SQL> SELECT * FROM transport_set_violations;

No rows should be displayed

If it were not self contained you should either remove the dependencies by dropping/moving them or include the tablespaces of segments into TTS set to which migration set is depended.

Commonly Asked Questions

Question	Short Answer	Complete Answer
Can I move/migrate to both a different RDBMS version and OS platform at the same time?	Yes; must be 10g or higher to move across OS platforms; charactersets must be the same regardless of version.	See "Limitations on Transportable Use" in Document 371556.1 How to move tablespaces across platforms using Transportable Tablespaces with RMAN
Do I have to convert the datafiles?	Yes, if the endianness is different.  If the endianness is not different and no undo is in any of the tablespaces being transported, then the convert step is not needed.	Document 243304.1 10g : Transportable Tablespaces Across Different Platforms confirms the answer. See question "Is there an easy-to-follow example?" below for another TTS-usage example.
Can I use TTS with ASM?	Yes, with RMAN, ASM files can be moved.	See "Transportable tablespace EXP/IMP of ASM files" in Document 371556.1 How to move tablespaces across platforms using Transportable Tablespaces with RMAN
Can I move raw files?	Yes, with RMAN.	See "Transportable tablespace EXP/IMP of ASM files" in Document 371556.1 How to move tablespaces across platforms using Transportable Tablespaces with RMAN
Can I transport just a single partition?	Yes.	See Document 731559.1 How to move or transport table partition using Transportable Table Space (TTS)?
Is there an easy-to-follow example?	Yes, see note.	See either "Transportable tablespace EXP/IMP of ASM files" or "Transportable tablespace EXP/IMP with OS files" in Document 371556.1 How to move tablespaces across platforms using Transportable Tablespaces with RMAN
Is there a size limitation?	No, except for a couple of size-related bugs, one which is a potential-corruption bug in < 11g.  Please see note for description and patch information.	Document 566875.1 Size Limitations On Cross Platform Transportable Tablespaces
What restrictions/limitations exist for TTS?	Movement between different charactersets Movement between different OS (depending on RDBMS version) Some objects are not transferred via TTS Oracle Server -- Standard Edition vs. Enterprise Edition; Standard Edition can only import TTS (no export) Also review size-related bugs in question above.	See "Limitations on Transportable Use" in Document 371556.1 How to move tablespaces across platforms using Transportable Tablespaces with RMAN See Feature Availability for Oracle Database Editions in the Oracle licensing documentation. See Document 114915.1 Using Dbms_tts.transport_set_check Results in Entry in Transport_set_violations See Document 883153.1 What Objects Are Exported With Transportble Tablespaces (TTS)?
What are best practices for TTS, especially when migrating a database?	Check restrictions/limitations in question above. If you are migrating a database, (a) make sure there are no invalid objects in the source database before making the export. Take a full norows export to recreate objects that won't be transported with TTS. Keep the source database viable until you have determined all objects are in the target database and there are no issues (i.e. the target database has been thoroughly checked out and exercised). Do a dry run to work out any unexpected issues and determine timings.

Common Issues with Transportable Tablespaces

Error/Problem	Suggested Solution
ORA-19721	Document 757795.1 Import of an Exported Transportable Tablespace get ORA-19721 error
ORA-19721, IMP-3, IMP-0 (< 11g)	ORA-19721 : Transportable Tablespace Import Fails With Errors IMP-00003,ORA-19721,IMP-00000 (Doc ID 438683.1)
ORA-29341 The transportable set is not self-contained	Document 114915.1 Using Dbms_tts.transport_set_check Results in Entry in Transport_set_violations Document 867246.1 New Partitions or Subpartitions Are Being Created in the Wrong Tablespace
TTS import completes successfully, but objects are missing	Document 883153.1 What Objects Are Exported With Transportble Tablespaces (TTS)? Also, 'CTXSYS, 'ORDSYS', 'MDSYS', 'ORDPLUGINS', 'LBACSYS', 'XDB', 'SI_INFORMTN_SCHEMA', 'DIP', 'DMSYS', 'DBSNMP are treated as objects owned by 'SYS' and not exported with TTS.