Transportable tablespaces

From Oracle FAQ
Jump to: navigation, search

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[edit]

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[edit]

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

ALTER TABLESPACE temp_ts READ ONLY;

Export the metadata[edit]

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[edit]

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

Import the metadata[edit]

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[edit]

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

ALTER TABLESPACE temp_ts READ WRITE;

Cleanup[edit]

Optionally drop the tablespace from the source database.