transportable tablespaces

References: metalink note:77523.1

Refer to the relevant database documentation, correct version for restrictions when using this feature.

note:

a Ensure the following scripts have been run in the database. These would normally be run at database creation time.

$ORACLE_HOME/rdbms/admin/catplug.sql
$ORACLE_HOME/rdbms/admin/dbmsplts.sql
$ORACLE_HOME/rdbms/admin/prvtplts.plb

sql> desc dbms_plugts

b If running this procedure by a user other than sys then ensure the user has dba privileges and PUBLIC SYNONYMS for the following following have been created.

DBMS_PLUGTS
DBMS_TTS


1. Identify tablespaces that need to be migrated. note: Dependent data objects need to be incapsuated in the process. i.e.: Data + Index objects need to be included.

2. Put relevent tablespaces into readonly mode.

sql> alter tablespace ... readonly;

3. Export metadata from tablesspaces
unix> exp userid/password file=... tablespaces=... transport_tablespaces=y

note: multiple tablespaces need to be delimited by a comma.

4. Copy datafiles that make up the tablespaces identified above into the target environment.

5. Copy the export dat file created in step 3.

6. Import metadata into target database.

unix> imp ... tablespaces=... datafiles=... transport_tablespaces=y
note: tablespaces and datafiles need to be seperated by comma's.

7. Verify tablespaces are updated correctly by querying relevent views, dba_tablespaces and v$datafile.

8. Put tablespaces into read write mode in source and target databases.
sql> alter tablespace ... read write;