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:
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
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
procedure takes two input parameters: the tablespace set and a Boolean
flag set to TRUE if you want to include referential integrity
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;
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;
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
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
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: