Transportable Tablespaces

Transportable tablespaces were introduced in Oracle8i to allow whole tablespaces to be copied between databases in the time it takes to copy the datafiles. In Oracle8i one of the restrictions was that the block size of both databases must be the same. In Oracle9i the introduction of multiple block sizes has removed this restriction. In this article I will run through a simple example of transporting the OEM_REPOSITORY tablespace from the W2K1 instance to the W2K2 instance.

For a tablespace to be transportable it must be totally self contained. This can be checked using the DBMS_TTS.TRANSPORT_SET_CHECK procedure. The TS_LIST parameter accepts a comma separated list of tablespace names and the INCL_CONSTRAINTS parameter indicates if constraints should be included in the check:

sys@w2k1> EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'OEM_REPOSITORY', incl_constraints => TRUE);

PL/SQL procedure successfully completed.

sys@w2k1>

The TRANSPORT_SET_VIOLATIONS view can be used to check for any violations:

sys@w2k1> SELECT * FROM transport_set_violations;

no rows selected

sys@w2k1>

Assuming no violations are produced we are ready to proceed by switching the tablespace to read only mode:

sys@w2k1> ALTER TABLESPACE oem_repository READ ONLY;

Tablespace altered.

sys@w2k1>

Next we export the tablespace metadata from W2K1 using the export utility:

C:> exp USERID='system/password@w2k1 AS SYSDBA' 
        TRANSPORT_TABLESPACE=y 
        TABLESPACES=oem_repository 
        FILE=oem_repository.dmp

With the export complete the datafile can be copied to the appropriate location on the W2K2 server and the source tablespace can be switched back to read write mode:

sys@w2k1> ALTER TABLESPACE oem_repository READ WRITE;

Tablespace altered.

sys@w2k1>

Next we must create any users in W2K2 that owned objects within this tablespace, assuming they do not already exist:

sys@w2k2> create user oem_marge identified by oem;

User created.

sys@w2k2>

Now we import the metadata into the W2K2 instance:

C:> imp USERID='system/password@w2k2 AS SYSDBA' 
        TRANSPORT_TABLESPACE=y 
        DATAFILES='C:\Oracle\oradata\W2K2\OEM_REPOSITORY.DBF' 
        TABLESPACES=oem_repository 
        FILE=oem_repository.dmp

Finally we switch the new tablespace into read write mode:

sys@w2k2> ALTER TABLESPACE oem_repository READ WRITE;

Tablespace altered.

sys@w2k2>

The tablespace is now available in the W2K2 instance:

sys@w2k2> select tablespace_name, plugged_in, status
  2  from   dba_tablespaces
  3  where  tablespace_name = 'OEM_REPOSITORY';

TABLESPACE_NAME                PLU STATUS
------------------------------ --- ---------
OEM_REPOSITORY                 YES ONLINE

sys@w2k2>

For further information see: Introduction to Transportable Tablespaces

Hope this helps. Regards Tim...

Back to the Top.