Oracle Transport Tablespace

- September 27, 2022

Transport tablespace is a feature of oracle database which is used to migrate application data from database to another database.

Transportabletablespace is a much-awaited new feature available in Oracle 8.1.5. This feature allows users to export the metadata of the tablespace instead of the data in the tablespace. By doing so, the consistent backup of the tablespace concerned could be transferred to the desired location and straightaway plugged in by importing the meta data instead of importing the whole data. This feature is very useful for data warehousing/applications which needs data to be distributed to various places. Using this feature, we can reduce the time required to export import the data. Transportable tablespace can also be used with RMAN, TSPITR. The examples given in this note are from NT. But the steps are identical even in Windows NT like any other Oracle Operations.

RESTRICTIONS

1. Operating system and processor type must be the same at source and target database

2. Database block size must be the same for the source and target database

3. Characterset of the source and the target must be the same

4. Users whose default tablespace is getting exported must exist in the target database before importing

5. Target database must not have tablespace of the same name

6. Oracle versions in source and target must be 8.1.x and above

7. Snapshot/replication, function-based indexes, Scoped REFs, domain indexes, 8.0-compatible advanced queues with multiple recipients are not supported

8. The tablespace must be self-contained to ensure that the table is complete in case of partitioning.

SETUP

The required scripts for transportable tablespaces are run by catproc.sql itself. Check that DBMS_PLUGTS and DBMS_TTS are valid. If not run the following scripts

$ORACLE_HOME/rdbms/admin/catplug.sql

$ORACLE_HOME/rdbms/admin/dbmsplts.sql

$ORACLE_HOME/rdbms/admin/plvtplts.plb

as 'SYS' user.

If you are importing the tablespace as a user other than 'SYS', create a public synonym for SYS.DBMS_PLUGTS as DBMS_PLUGTS else you would get the errors similar to the following

import done in US7ASCII character set and US7ASCII NCHAR character set

IMP-00003: ORACLE error 6550 encountered

ORA-06550: line 1, column 7:

PLS-00201: identifier 'DBMS_PLUGTS.NEWTABLESPACE' must be declared

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

IMP-00000: Import terminated unsuccessfully

The user should also have dba privilege.

Below are the steps to migrate tablespace.

1) Violation check

execute dbms_tts.transport_set_check('data_test,data_INDEX',TRUE);

Made the tablespace READ only