Transportable Tablespaces

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

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 block size
·    Must have the same character set
        select * from NLS_DATABASE_PARAMETERS
      Parameter NLS_NCHAR_CHARACTERSET should match in both databases
·    Be on the same operating system
·    Oracle versions in source and target must be 8.1x and above
·    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

Generating a Transportable Tablespace Set
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.

If you will be moving tables among databases frequently, having a generic DATA tablespace and an INDEXES tablespace is no longer adequate. You should dedicate specific tablespaces to the tables that you will regularly move and store the indexes for those tables in dedicated tablespaces also, to facilitate the creation of the transportable tablespace set.

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, 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');

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;

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

Plugging 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. 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

Management Issues

The database-block size in the source and the target database must be the same.
Same Character Set and National Language Character Set for both databases
Same operating system for both databases
All data must be self-contained: indexes and tables on the same tablespace, BLOBs and data segments on the same tablespace, parent and child tables in a primary key-foreign key relationship on the same tablespace (though you can choose not to transport all of the PK-FK data), all partitions for a table must be on the same tablespace, etc
Currently, the transport set cannot contain snapshots, function-based indexes, domain indexes, scoped REFs, and advanced queues.

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:

Create a new tablespace to store the data you want to move.
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.
Prepare the new tablespace for the move by placing it in read-only mode.
Check the transport set by using the DBMS_TTS.TRANSPORT_SET_CHECK procedure.
Export the metadata for the new tablespace.
At the operating-system level, move the new tablespace's datafiles and the export dump file to the target location.
Once you have completed and verified step 6, drop the tablespace from the source database.
Import the metadata for the new tablespace into the target database.
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.
Commit the inserted data, and verify that everything worked.
Back up the control files for the source and the target database.