Transportable tablespaces

Transportable tablespaces is a feature of the Oracle database, introduced with Oracle 8i, that allows DBAs to copy or move tablespaces between databases.

Test if tablespace can be transported

The first step is to test if a given tablespace or set of tablespaces are self-contained and ready for transportation:

EXEC sys.dbms_tts.transport_set_check('temp_ts', TRUE);
SELECT * FROM sys.transport_set_violations;

One can also use the TRANSPORT_FULL_CHECK=y parameter with expdb to perform this test in Oracle 10g and above.

Mark the tablespace as READ-ONLY

Execute the following SQL statement to mark the tablespace as read-only:

ALTER TABLESPACE temp_ts READ ONLY;

Export the metadata

Export the metadata from the source database. For 10g and later versions:

expdp \'sys/oracle as sysdba\' TRANSPORT_TABLESPACES=temp_ts TRANSPORT_FULL_CHECK=y

Releases before 10g:

exp system/manager transport_tablespace=yes tablespaces=temp_ts triggers=no constraints=no

Copy/ move the physical files

Use a FTP (binary mode) or copy program to relocate the physical data files from the source to the target systems.

Import the metadata

Import the metadata on the target database. For 10g and later versions:

impdp \'sys/oracle as sysdba\' TRANSPORT_DATAFILES=/tmp/test_ts_file1.dbf DUMPFILE=expdat.dmp

Releases before 10g:

imp system/manager transport_tablespace=yes tablespaces=temp_ts datafiles=\('df1,df2,...'\)

Mark the tablespace as READ WRITE

Execute the following SQL statement to mark the tablespace as read-write:

ALTER TABLESPACE temp_ts READ WRITE;

Cleanup

Optionally drop the tablespace from the source database.