We can use the Transportable Tablespaces feature to copy a set of tablespaces from one Oracle Database to another. The tablespaces being transported can be either dictionary managed or locally managed. Moving data using transportable tablespaces is much faster than performing either an export/import or unload/load of the same data. This is because the datafiles containing all of the actual data are just copied to the destination location, and you use Data Pump to transfer only the metadata of the tablespace objects to the new database.
In this article I will run through a simple example of transporting a tablespace between two oracle 11g (11.2.0.3) databases followed by more concepts, features and limitations of Oracle Transportable Tablespaces.
Example of Transportable Tablespace
1. On Source Database Server:
For this example I'm going to create a new tablespace, user and table to work with in the source database.
$ export ORACLE_SID=test
$ connect / AS SYSDBA
SQL> CREATE TABLESPACE mytestts DATAFILE '/u01/data/mytestts01.dbf' SIZE 1M AUTOEXTEND ON NEXT 1M;
SQL> ALTER TABLESPACE mytestts ADD DATAFILEe '/u01/data/mytestts02.dbf' SIZE 1M AUTOEXTEND ON NEXT 1M;
SQL> CREATE USER test_user IDENTIFIED BY test_user DEFAULT TABLESPACE mytestts TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON mytestts;
SQL> GRANT CREATE SESSION, CREATE TABLE TO test_user;
SQL> CONN test_user/test_user
SQL> CREATE TABLE test_table (
id NUMBER,
description VARCHAR2(50),
CONSTRAINT test_table_pk PRIMARY KEY (id)
);
SQL> INSERT /*+ APPEND */ INTO test_table (id, description)
SELECT level,
'Description for '
level
FROM dual
CONNECT BY level <= 10000;
SQL> COMMIT;
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.
SQL>EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'mytestts', incl_constraints => TRUE);
The TRANSPORT_SET_VIOLATIONS view is used to check for any violations.
SQL>SELECT * FROM transport_set_violations;
Assuming no violations are produced we are ready to proceed by switching the tablespace to read only mode.
SQL> ALTER TABLESPACE mytestts READ ONLY;
We can now export the tablespace metadata.
$ expdp userid=system/password directory=temp_dir transport_tablespaces=mytestts dumpfile=mytestts.dmp logfile=mytestts.log
If you are using a version prior to 10g, you do not need the directory object and your command would look something like this.
$ exp userid='system/password as sysdba' transport_tablespace=y tablespaces=test_data file=test_data.dmp log=test_data_exp.log
Now, copy the datafile to the appropriate location on the destination database server. Also copy the dump file to a suitable place on the destination database server. You may use binary FTP or SCP to perform this copy.
The source tablespace can now be switched back to read/write mode.
SQL> ALTER TABLESPACE mytestts READ WRITE;
2. On Target Database Server:
Create any users in the destination database that owned objects within the tablespace being transported, assuming they do not already exist.
$ export ORACLE_SID=uatdb
SQL> CONN / AS SYSDBA
SQL> CREATE USER test_user IDENTIFIED BY test_user;
SQL> GRANT CREATE SESSION, CREATE TABLE TO test_user;
Now we import the metadata into the destination database.
$ impdp userid=system/password directory=mytempdir dumpfile=mytestts.dmp logfile=mytestts_imp.log
transport_datafiles=/u01/data/mytestts01.dbf,/u01/data/mytestts02.dbf
If you are using a version prior to 10g, you do not need the directory object and your command would look something like this.
$ imp userid='system/password as sysdba' transport_tablespace=y datafiles='/u01/app/oracle/oradata/DB11GB/test_data01.dbf' tablespaces=test_data file=test_data.dmp log=test_data_imp.log
Now, switch the new tablespace into read write mode.
SQL> ALTER TABLESPACE test_data READ WRITE;
The tablespace is now available in the destination database.
SQL> SELECT tablespace_name, plugged_in, status FROM dba_tablespaces WHERE tablespace_name = 'MYTESTTS';
TABLESPACE_NAME PLUGGED STATUS
------------------------------ --- ---------
MYTESTTS YES ONLINE
Theory on Oracle's Transportable Tablespaces.
With Oracle 8i, Oracle introduced transportable tablespace (TTS) technology that moves tablespaces between databases. Oracle 8i supports tablespace transportation between databases that run on same OS platforms and use the same database block size.
With Oracle 9i, TTS (Transportable Tablespaces) technology was enhanced to support tablespace transportation between databases on platforms of the same type, but using different block sizes.
With Oracle 10g, TTS (Transportable Tablespaces) technology was further enhanced to support transportation of tablespaces between databases running on different OS platforms (e.g. Windows to Linux, Solaris to HP-UX), which has same ENDIAN formats. Oracle Database 10g Release 1 introduced cross platform transportable tablespaces (XTTS), which allows data files to be moved between platforms of different endian format. XTTS is an enhancement to the transportable tablespace (TTS). If ENDIAN formats are different we have to use RMAN (e.g. Windows to Solaris, Tru64 to AIX).
From 10g Release2 we can transport whole database, this is called Transportable Database (TDB).
From Oracle 11g, we can transport single partition of a tablespace between databases.
You can also query the V$TRANSPORTABLE_PLATFORM view to see all the platforms that are supported, and to determine their platform names and IDs and their endian format.
SQL> select * from v$transportable_platform order by platform_id;
Transporting tablespaces is particularly useful for
(i) Updating data from production to development and test instances.
(ii) Updating data from OLTP systems to data warehouse systems.
(iii) Transportable Tablespace (TTS) is used to take out of the database pieces of data for various reasons (Archiving, Moving to other databases etc).
(iv) Performing tablespace point-in-time-recovery (TS PITR).
Moving data using transportable tablespaces can be much faster than performing either an export/import or unload/load of the same data, because transporting a tablespace only requires copying of datafiles and integrating the tablespace structural information. You can also use transportable tablespaces to move both table and index data, thereby avoiding the index rebuilds you would have to perform when importing or loading table data.
In Oracle 8i, there were three restrictions with TTS. First, both the databases must have same block size. Second, both platforms must be the same OS. Third, you cannot rename the tablespace. Oracle 9i removes the first restriction. Oracle 10g removes the second restriction. Oracle 10g also makes available a command to rename tablespaces.
Limitations/Restrictions
Following are limitations/restrictions of transportable tablespace:
System, undo, sysaux and temporary tablespaces cannot be transported.
The source and target database must be on the same hardware platform. We can transport tablespaces between Sun Solaris databases, or we can transport tablespaces between Windows NT databases. However, you cannot transport a tablespace from a Sun Solaris database to a Windows NT database.
The source and target database must use the same character set and national character set.
If Automatic Storage Management (ASM) is used with either the source or destination database, you must use RMAN to transport/convert the tablespace.
You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the tablespace to be transported or the destination tablespace before the transport operation.
Transportable tablespaces do not support: Materialized views/replication Function-based indexes.
We can't do TTS when we are using TDE concept for our database.
Binary_Float and Binary_Double datatypes (new in Oracle 10g) are not supported.
References: http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces013.htm
