Transportable Tablespaces[1]

before covering the details of how to setup and use transportable tablespaces, let's first discuss some of the terminology and limitations to provide us with an introduction.

• the use of transportable tablespaces are much faster than using export/import, sql*plus copy tables, or backup and recovery options to copy data from one database to another.

• a transportable tablespace set is defined as two components:
  • all of the datafiles that make up the tablespaces that will be moved.

    and

  • an export that contains the data dictionary information about those tablespaces.

• compatible must be set in both the source and target database to at least 8.1.

• when transporting a tablespace from an oltp system to a data warehouse using the export/import utility, you will most likely not need to transport trigger and constraint information that is associated with the tables in the tablespace you are exporting. that is, you will set the triggers and constraints export utility parameters equal to "n".

• the data in a data warehouse is inserted and altered under very controlled circumstances and does not require the same usage of constraints and triggers as a typical operational system does.

• it is common and recommended though that you use the grants option by setting it to y.

• the triggers option is new in oracle8i for use with the export command. it is used to control whether trigger information, associated with the tables in a tablespace, are included in the tablespace transport.

limitations of transportable tablespaces:

• the transportable set must be self-contained.

• both the source and target database must be running oracle 8.1 or higher release.

• the two databases do not have to be on the same release

• the source and target databases must be on the same type of hardware and operating-system platform.

• the source and target databases must have the same database block size.

• the source and target databases must have the same character set.

• a tablespace with the same name must not already exist in the target database.

• materialized views, function-based indexes, scoped refs, 8.0 compatible advanced queues with multiple-recipients, and domain indexes can't be transported in this manner. (as of oracle8i)