Transportable Tablespace

Transportable tablespace is technique by which we can transport a tablespace from one database to another database.
Source Database => This is the database from where we will transport the database.
Target Database  => This is the database where transported tablespace wiil be imported.

We need to follow some steps in order to transport the tablespace.

Create a tablespace  to be transported

SYS>CREATE TABLESPACE tts DATAFILE ‘/disk1/oradata/tts.dbf’ SIZE 4m;

On source database
There are a number of steps to be done on the source site. Respectively,

1. Validating the self containing property of the migration tablespace.
2. Altering the migration tablespace to read only mode.
3. Exporting the meta data

1.Check Self Containing Property

SYS>exec dbms_tts.transport_set_check(‘TTS’);

This is going to populate a table if TTS is not self contained.

SYS>select  *  from transport_set_violations;

No row selected

If it were not self contained you should remove the dependencies by dropping them.

2.ALTER Tablespaces to READ ONLY Mode

SYS>alter tablespace tts read only;

3.Export the Metadata

We will export the metadata of the tablespace with export utility and will transfer datafile and dumpfile to target server using FTP. Since we export metadata only hence operation will be fast.

]$ exp file=tts.dmp log=tts.log transport_tablespaces=y tablespaces=tts

Target Database

On target database we need to do-

1. Copy the datafiles and export file to target server via FTP
2. Create the necessary schemas
3. Import the export file
4. make tablespace read write.
   

2. Create all users on target database who were working on tts tablespace on source database.

3.Now import the tablespace

]$ imp file=tts.dmp log=tts.log transport_tablespaces=y datafiles=/disk2/oradata/tts.dbf

4. Make tablespace read write 

SYS>alter tablespace tts read write;