Transportable Tablespaces

 
This new feature of transportable  tablespaces has given new dimension to DBA'S in managing the databases effectively .This new feature bypasses usual data extract and load processes and enables fast bulk data transport between  databases .Transport Tablespace mechanism consist of combination of 2 things:

• An export of metadata(instead of data) of tablespace
  
• A copy of files belonging to tablespace 

These two parts can be transferred to another compatible system  in order to plug in tablespace with its files on another database Restore files to new location . Import metadata of tablespace specifying new location of files by transporting tablespaces between databases data movement can be fast as a simple  transfer of files between  machines which greatly improves the performance and provides operational simplicity.
A transportable tablespace set contains all the datafiles for the tablespaces you are moving along with an export of the metadata for those tablespaces. The tablespaces you wish to transport should be self-contained - they should not contain any objects that depend on objects outside the tablespaces in the set. For example, if you want to move a table, you must also transport the tablespace that contains the table's indexes. The better you organize and distribute your objects among tablespaces, the easier it is to generate a self-contained set of tablespaces for transport.

BENEFITS  OF TRANSPOTABLE TABLESPACES
- Move  entire tablespace data
- Supports media recovery
- Reduces Server Burden - When transportable tablespaces replace large export/import or other loading,a significant processing burden disappears.
- Higher Reliability - Fewer processing steps and quicker processing reduce the opportunities for human Errors and system problems.
 
  LIMITATIONS OF TRANSPORTABLE TABLESPACES
- Must have the same character set and and same national character set.
        select * from NLS_DATABASE_PARAMETERS
      Parameter  NLS_NCHAR_CHARACTERSET should match in both databases
- Be on the same operating system (For 9i, not for 10g)
- Snapshot/replication ,function based indexes, domain indexes are not supported .   
- The tablespace must be self contained to ensure table is complete e.g in case of partitioning or in case of  integrity constraints
- System, undo, sysaux, and temporary tablespaces cannot be transported. Doing so will kick off the following errors.
          ORA-29351: can not transport system, sysaux, or temporary tablespace

Steps for Migration
1-Verify Tablespace is self-contained
You can optionally choose whether to include referential integrity constraints as part of the transportable tablespace set. If you choose to do so, the tables required to maintain the key relationships will increase the size of the transportable tablespace set. Including referential integrity constraints is optional, because you may refer to the same tables in multiple databases. Say you plan to move a tablespace from a test database that contains a table called COUNTRY to a production database that already contains a COUNTRY table of identical country codes. Because the two databases have identical tables, you do not need to transport the referential integrity constraints. You could transport the tablespace and then reenable the referential integrity constraints in the target database once you've moved the tablespace, simplifying the creation of the transportable tablespace set. You must always export primary-key constraints.

To find out if a tablespace set is self-contained, execute the TRANSPORT_SET_CHECK procedure as SYS User, which is in the DBMS_TTS package. This procedure takes two input parameters: the tablespace set and a Boolean flag set to TRUE if you want to include referential integrity constraints.
In this example, constraints aren't included in the AGG_DATA and AGG_INDEXES tablespace checks:
    execute DBMS_TTS.TRANSPORT_SET_CHECK ('AGG_DATA,AGG_INDEXES','FALSE');

Then execute the following to report violations:
        SELECT * FROM transport_set_violations; 

2- Put Tablespace/s in Read Mode
Once you have verified that the selected tablespace set is self-contained, make the tablespaces read only, as follows:
alter tablespace AGG_DATA read only;
alter tablespace AGG_INDEXES read only;

3- Export MetaData of Tablespace
Next, export the metadata for the tablespaces, using the TRANSPORT_TABLESPACES and TABLESPACES parameters of the Oracle Export utility:
exp user/pwd TRANSPORT_TABLESPACE=Y TABLESPACES=(AGG_DATA,AGG_INDEXES) CONSTRAINTS=N GRANTS=Y TRIGGERS=N
 

4- Copy and Plugg in the Transportable Tablespace Set
Once you have moved the transportable tablespace set to an area the target database can access, you can plug the set into the target database. First, use the Oracle Import utility to import the exported metadata:
imp user/pwd TRANSPORT_TABLESPACE=Y DATAFILES=(agg_data.dbf,agg_indexes.dbf)

In the import command, you specify the datafiles that are part of the transportable tablespace set. When the import has finished, all of the new tablespaces in the transportable tablespace set are in read-only mode.

5- Modify Tablespaces to read-write Mode
To change them to read-write mode, you issue the alter tablespace read write command in the target database:
alter tablespace AGG_DATA read write;
alter tablespace AGG_INDEXES read write;

After you've made these changes, you should back up the control files of both the target and the source database:
alter database backup controlfile to trace;

Note that instead of importing the table's data, you have imported only the tablespaces' metadata - a significant performance savings. When you import the data into the target database, the schema owner must already exist - the import does not create a user
 

What About Partitions?
When you move a table, you have to move the entire table. If you have partitioned a table, you can't transport just one partition - it's all or nothing. If you have a large table with many partitions and you want to use the transportable tablespace feature to move one partition's worth, you can follow these steps:

1. Create a new tablespace to store the data you want to move.
   
2. In the new tablespace, create a table for the data you want to move. For example, you can use a create table as select command with a where clause that selects only the current month's data and puts it into the new table. Optionally, you can create indexes for the new table, in either the new tablespace or in a separate isolated one.
3. Prepare the new tablespace for the move by placing it in read-only mode.
   
4. Check the transport set by using the DBMS_TTS.TRANSPORT_SET_CHECK procedure.
   
5. Export the metadata for the new tablespace.
   
6. At the operating-system level, move the new tablespace's datafiles and the export dump file to the target location.
   
7. Once you have completed and verified step 6, drop the tablespace from the source database.
   
8. Import the metadata for the new tablespace into the target database.
   
9. Insert the data into the tables in the target database. You can do this via insert as select or, if the target table is partitioned, by using the exchange partition option to make the newly plugged-in table a partition within the target table.
   
10. Commit the inserted data, and verify that everything worked.
    
11. Back up the control files for the source and the target database.