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.

Contents

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

[edit] Mark the tablespace as READ-ONLY

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

ALTER TABLESPACE temp_ts READ ONLY;

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

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

[edit] 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,...'\)

[edit] Mark the tablespace as READ WRITE

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

ALTER TABLESPACE temp_ts READ WRITE;

[edit] Cleanup

Optionally drop the tablespace from the source database.