In Oracle Database 10g, you can transport tablespaces between different platforms.

Transportable tablespaces are a good way to migrate a database between different platforms.

You must be using the Enterprise Edition of Oracle8i or higher to generate a transportable tablespace set. However, you can use any edition of Oracle8i or higher to plug a transportable tablespace set into an Oracle Database on the same platform.

To plug a transportable tablespace set into an Oracle Database on a different platform, both databases must have compatibility set to at least 10.0.

Many, but not all, platforms are supported for cross-platform tablespace transport. You can query the V$TRANSPORTABLE_PLATFORM view to see the platforms that are supported.

• The source and target database must use the same character set and national set.

• Objects with underlying objects (such as materialized views) or contained objects (such   as partitioned tables) are not transportable unless all of the underlying or contained   objects are in the tablespace set.

• You cannot transport the SYSTEM tablespace or objects owned by the user SYS.

1. Check endian format of both platforms. For cross-platform transport, check the endian
format of both platforms by querying the V$TRANSPORTABLE_PLATFORM view.

You can find out your own platform name:

2. Pick a self-contained set of tablespaces.

The following statement can be used to determine whether tablespaces sales_1 and sales_2 are selfcontained, with referential integrity constraints taken into consideration:

Note: You must have been granted the EXECUTE_CATALOG_ROLE role (initially signed to SYS) to execute this procedure.

You can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS view. If the set of tablespaces is self-contained, this view is empty.

3. Generate a transportable tablespace set.

4. Transport the tablespace set.

Transport both the datafiles and the export file of the tablespaces to a place accessible to the target database.

5. Convert tablespace set, if required, in the destination database.

Use RMAN as follows:

Note: The source and destination platforms are optional.

Note: By default, Oracle places the converted files in the Flash Recovery Area, without changing the datafile names.

Note: If you have CLOB data on a small-endian system in an Oracle database version before 10g and with a varying-width character set and you are transporting to a database in a big-endian system, the CLOB data must be converted in the destination database. RMAN does not handle the conversion during the CONVERT phase. However, Oracle database automatically handles the conversion while accessing the CLOB data.

If you want to eliminate this run-time conversion cost from this automatic conversion, you can issue the CREATE TABLE AS SELECT command before accessing the data.

6. Plug in the tablespace.

If required, put the tablespace into READ WRITE mode.

o There are a few restrictions on what tablespaces can qualify for    transportability:

o You cannot transport the SYSTEM tablespace or any of its contents. This    means that you cannot use TTS for PL/SQL, triggers, or views. These    would have to be moved with export.

o The source and target database must have the same character set and    national language set.

o You cannot transport a table with a materialized view unless the mview is    in the transport set you create.

o You cannot transport a partition of a table without transporting the entire    table.

1. In a staging database, you create a new tablespace and make it contain the table you    want to transport. It should have the same columns as the destination partitioned table.

2. Create an index on the same columns as the local index in the partitioned table.

3. Transport the tablespace to the data warehouse.

4. In the data warehouse, add a partition to the table.

5. Attach the transported table to the partitioned table by exchanging it with the new    partition:

A data provider can load a tablespace with data to be published, generate the transportable set, and copy the transportable set to a CD. When customers receive this  CD, they can plug it into an existing database without having to copy the datafiles from the CD to disk storage.

Note: In this case, it is highly recommended to set the READ_ONLY_OPEN_DELAYED initialization parameter to TRUE.

You can use transportable tablespaces to mount a tablespace read-only on multiple databases.

You can use the Transport Tablespaces wizard to move a subset of an Oracle database from one Oracle database to another, even across different platforms.

The Transport Tablespaces wizard automates the process of generating a transportable tablespace set, or integrating an existing transportable tablespace set.

The wizard uses a job that runs in the Enterprise Manager job system.