Transportable Tablespaces (TTS) in Oracle

--Limitation

1. System, undo, sysaux and temp tablespaces cannot be transported.
2. The source and target database must use the same character set and national character set. 
3. IF ASM is used then must use RMAN to transport/convert the tablespace.
3. We can not transport the same name tablespace which is already exists in destination database
4. Do not support: Materialized views/replication Function-based indexes

We can also query the V$TRANSPORTABLE_PLATFORM  to find  the platforms that are supported, and to determine their platform names with their endian format.

       


  
SELECT P.PLATFORM_NAME, P.ENDIAN_FORMAT FROM V$DATABASE D, V$TRANSPORTABLE_PLATFORM P WHERE D.PLATFORM_NAME=P.PLATFORM_NAME ;
  
  
  
  

       
 

1. Validate the tablespace,which we are moving must be self contained ,does not have any dependency on other tablespace.

       


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



SQL> SELECT * FROM transport_set_violations;
  
  
  

       
 

---this should be no rows, if found any then solve it by either dropping or removing that dependency to make tablespace self contained

2.Make the Tablespace Read-only mode

       


  
SQL> alter tablespace tts read only;
  
  
  

       
 

3. make the dump file 

       


  
expdp system/oracle DUMPFILE=tts.dmp LOGFILE=tts_exp.log DIRECTORY=njbackup TRANSPORT_TABLESPACES=tts TRANSPORT_FULL_CHECK=y
  
  

       
 

--If the tablespace is not self-contained, then the export will fail.

4. Now We can drop the tablespace or make in read write mode (once it restore on destination database then dropping tablespace would be better option)

       


  
  
  SQL> drop tablespace tts including contents;

OR

SQL> alter tablespace tts read write;
  
  

       
 

5. copy the datafiles at destination location

--At Destination Database

6. create the same name user with same privileges or use Remap Schema 

7. import the tablespace 

       


  
impdp system/oracle@tts DUMPFILE=tts.dmp LOGFILE=tts_imp.log DIRECTORY=njbackup TRANSPORT_DATAFILES='/u01/app/oradata/TTS/tts01.dbf'
  
  

       
 

8. Make the Tablespace Read-only mode

       


  
SQL> alter tablespace tts read write;
  
  
  

       
 

Thanks,