Using Transportable Tablespaces

Oracle transportable tablespaces are the fastest way for moving large volumes of data between two Oracle databases. Using transportable tablespaces, Oracle data files (containing table data, indexes, and almost every other Oracle database object) can be directly transported from one database to another. Furthermore, like import and export, transportable tablespaces provide a mechanism for transporting metadata in addition to transporting data.

The transportable tablespace feature is useful in a number of scenarios, including:

• Exporting and importing partitions in data warehousing tables
• Publishing structured data on CDs
• Copying multiple read-only versions of a tablespace on multiple databases
• Archiving historical data
• Performing tablespace point-in-time-recovery (TSPITR)
• Migrating databases among RDBMS versions and OS platforms

RESTRICTIONS
1. Operating system and processor type must be the same at source and target database (restriction for 8i and 9i target databases; allowed in 10g and higher target databases)
2. Database block size must be the same for the source and target database (restriction for 8i target database; allowed in 9i and higher target databases)
3. Character set as well as national character set 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- The tablespace must be self contained to ensure that the table is complete in case of partitioning.


Example Environment: 
Source: Oracle Database 11gR2 (11.2.0.3) on Windows 7 (64bit)
Target: Oracle RAC Database (2 node) 11gR2 (11.2.0.3) On Solaris 10 x86

SETUP
1-  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 as sysdba

SQL> @C:\app\Inam\product\11.2.0\dbhome_1\RDBMS\ADMIN\catplug.sql
SQL> @C:\app\Inam\product\11.2.0\dbhome_1\RDBMS\ADMIN\dbmsplts.sql
SQL> @C:\app\Inam\product\11.2.0\dbhome_1\RDBMS\ADMIN\prvtplts.plb

2- We shall consider a tablespace by name "TS1" for the example.

a) Create a tablespace.
SQL> create tablespace ts1 datafile 'C:\app\Inam\oradata\orcl\ts01.dbf' size 10m reuse;

b) Create a user and create a table in the new tablespace
SQL>create user ali identified by ali;
SQL>grant dba to ali;
SQL>conn ali/ali
SQL> create table objects tablespace ts1 as select * from dba_objects ;

c) Make the tablespace readonly. SQL> conn / as sysdba
Connected.
SQL> alter tablespace ts1 read only;

Tablespace altered.
3- Export the tablespace specifying the TABLESPACES and TRANSPORT_TABLESPACE clause
C:\Windows\system32>exp '/ as sysdba' file=c:\temp\tt_ts1.dmp tablespaces=ts1 tr
ansport_tablespace=y

Export: Release 11.2.0.3.0 - Production on Sun Feb 24 13:56:37 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TS1 ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                        OBJECTS
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

 

C:\Windows\system32>

4- Transfer the meta data export dump file and the copy of the datafile(s) to the target (Solaris 10 in our case) database . (Could be ftp or copy or via cdrom or any other compatible media)

5- Import the metadata using TABLESPACES, TRANSPORT_TABLESPACE, DATAFILES clause If more than one datafiles are available then specify all of them comma separated.
Following are on Target (Solaris) using Oracle user (owner of DB)

bash-3.2$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
bash-3.2$ export ORACLE_SID=racsoldb1
bash-3.2$ cd /u01/app/oracle/product/11.2.0/db_1/bin
bash-3.2$ ./imp "'"/ as sysdba"'" file=/tmp/tts/tt_ts1.dmp TABLESPACES=ts1 TRANSPORT_TABLESPACE=Y DATAFILES=/tmp/tts/ts01.dbf

Import: Release 11.2.0.1.0 - Production on Sun Feb 24 14:01:14 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes

Export file created by EXPORT:V11.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing ALI's objects into ALI
. . importing table                      "OBJECTS"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
bash-3.2$

 
Ref: 1166564.1,243304.1, 77523.1