Syntax and Description
TRANSPORT_TABLESPACES=tablespace_name [, ...]
Use the TRANSPORT_TABLESPACES parameter to specify a list of tablespace namesfor which object metadata will be exported from the source database into the targetdatabase.
Note: You cannot export transportable tablespaces and then importthem into a database at a lower release level. The target database mustbe at the same or higher release level as the source database.
Transportable jobs are not restartable.
Transportable jobs are restricted to a degree of parallelism of 1.
Transportable tablespace mode requires that you have the EXP_FULL_DATABASE role.
The following is an example of using the TRANSPORT_TABLESPACES parameter in afile-based job (rather than network-based). The tablespace tbs_1 is the tablespacebeing moved. This example assumes that tablespace tbs_1 exists and that it has beenset to read-only.
> expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=tts.dmpTRANSPORT_TABLESPACES=tbs_1 TRANSPORT_FULL_CHECK=y LOGFILE=tts.logtaken from Oracle Database Utilities 10g Release 2.pdf page 84 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.