Data Movement and Transportable Tablespaces
This domain tests moving tablespaces and data between databases efficiently, including the self-contained check, Data Pump transport syntax, and cross-platform conversion.
Transportable tablespaces
Make the set read-only, then verify it is self-contained with DBMS_TTS.TRANSPORT_SET_CHECK and query TRANSPORT_SET_VIOLATIONS. A self-contained set has no object dependencies pointing outside the set. Export metadata with Data Pump using TRANSPORT_TABLESPACES=hr,orders and TRANSPORT_FULL_CHECK=Y, copy the datafiles and dump file to the target, then import with TRANSPORT_DATAFILES=.... Users, roles, and grants are not transported; create or pre-create them on the target.
Cross-platform movement
Query V$TRANSPORTABLE_PLATFORM for platform names and endian formats. If source and target differ in endianness, convert with RMAN CONVERT DATAFILE ... TO PLATFORM '...' or CONVERT TABLESPACE .... Data Pump can also perform conversion with TRANSPORTABLE=ALWAYS in suitable cases. Full transportable export moves an entire database's user tablespaces plus metadata.
Other movement tools
Data Pump expdp/impdp supports NETWORK_LINK, REMAP_SCHEMA, REMAP_TABLESPACE, PARALLEL, and FLASHBACK_SCN for consistency. SQL*Loader and external tables load flat files. Choose transportable tablespaces for large, self-contained data volumes where the metadata overhead of row-by-row export is prohibitive.
Worked example
Move the HR tablespace to a new database: ALTER TABLESPACE hr READ ONLY; run the transport set check, then expdp system/... DIRECTORY=dpump DUMPFILE=hr.dmp TRANSPORT_TABLESPACES=hr TRANSPORT_FULL_CHECK=Y LOGFILE=hr.log. Copy hr.dmp and the HR datafiles, then impdp ... DUMPFILE=hr.dmp TRANSPORT_DATAFILES='/u02/oradata/tgt/hr01.dbf'. Finally make the tablespace read-write on the target.
Traps
The tablespace set must be read-only and self-contained; violations block the transport. SYSTEM, SYSAUX, and undo tablespaces cannot be transported this way. Forgetting endian conversion produces an unusable datafile. Import does not create application users automatically.