Transportable Tablespace (TTS)

'Transportable Tablespace' (TTS) first introduce in Oracle 8i and it become matured as time goes by.  You can use this  feature to copy a set of tablespaces from one Oracle Database to another.


For a DBA, transport data from one database to another is a very common task and you can do it in different ways

Method           Hardness     Best Suitable For    Availability

Database Link        Easy           Less than 50 GB           High
Data Pump           Moderate     Over 50 GB                 High
TTS                 Moderate     Terabytes of Data        Low
RMAN Duplicate    Hard           Full Database              High




Requirements:

• A tablespace must be totally self contained to be transportable.
• The source and target database must use the same character set and national character set.
• You cannot transport a tablespace to a target database in which a tablespace with the same name already exists.
• 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.
• There are some limitations in encryption tables.

Lets see how it works.................
 
IN SOURCE DATABASE


1. Create two tablespaces and one user

sqlplus / as sysdba;

CREATE TABLESPACE TT01 DATAFILE '/oradata/tt_01.dbf' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED BLOCKSIZE 8K EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M SEGMENT SPACE MANAGEMENT AUTO ONLINE ;

CREATE TABLESPACE TT02 DATAFILE '/oradata/tt_02.dbf' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED BLOCKSIZE 16K EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M SEGMENT SPACE MANAGEMENT AUTO ONLINE ;


CREATE USER test identified by test default tablespace tt01;
GRANT connect,resource to test;

2. Create a table with some data

conn test/test;

CREATE TABLE test_data(
id number (10),
alphabate varchar2 (10)
) TABLESPACE tt01;

ALTER TABLE test_data ADD CONSTRAINT PK_TEST_DATA_ID PRIMARY KEY (ID) using index tablespace tt02;

INSERT INTO test_data VALUES (1,'A');
INSERT INTO test_data VALUES (2,'B');
INSERT INTO test_data VALUES (3,'C');
INSERT INTO test_data VALUES (4,'D');
INSERT INTO test_data VALUES (5,'E');

commit;

SELECT segment_name, tablespace_name from user_segments;

SEGMENT_NAME       TABLESPACE_NAME
------------------ ------------------
TEST_DATA          TT01
PK_TEST_DATA_ID    TT02

So we have a table in one tablespace and an unique index (creat for primary key) in another tablespace.



3. tablespace's 'self contained' Test

conn / as sysdba;

EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TT01', incl_constraints => TRUE);

SELECT * FROM sys.transport_set_violations;

VIOLATIONS
------------------------------------------------------------------------------------------------------------------------------------
ORA-39908: Index TEST.PK_TEST_DATA_ID in tablespace TT02 enforces primary constraints  of table TEST.TEST_DATA in tablespace TT01.

we got this error because TT01 tablespace contain a table which has an index stored in another tablespace.

EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TT01,TT02', incl_constraints => TRUE);

SQL> SELECT * FROM sys.transport_set_violations;

no rows selected

This time no Error because all two tablespaces are included. Now make them 'Read Only'.


ALTER TABLESPACE tt01 READ ONLY;
ALTER TABLESPACE tt02 READ ONLY;


4. Take metadata of those 2 tablespaces by using datapump

expdp system directory=dump_dir transport_tablespaces=tt01,tt02 dumpfile=trans_tablespaces.dmp logfile=trans_tablespaces.log

Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/ ******** directory=dump_dir transport_tablespaces=tt01,tt02 dumpfile=trans_tablespaces.dmp logfile=trans_tablespaces.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /d01/dump_dir/trans_tablespaces.dmp
******************************************************************************
Datafiles required for transportable tablespace TT01:
  /oradata/tt_01.dbf
Datafiles required for transportable tablespace TT02:
  /oradata/tt_02.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 02:27:17


5. Copy the datafiles to destination database

scp tt_01.dbf oracle@db2:/oradata/
scp tt_02.dbf oracle@db2:/oradata/

6. Make the read_only tablespaces to read write mode

sqlplus / as sysdba

ALTER TABLESPACE tt01 READ WRITE;
ALTER TABLESPACE tt02 READ WRITE;


IN DESTINATION DB

1. Create a user same as source DB

sqlplus / as sysdba;

CREATE USER test identified by test ;
GRANT connect,resource to test;


2. Import the metadata

impdp system directory=dump_dir dumpfile=trans_tablespaces.dmp transport_datafiles='/oradata/tt_01.dbf','/oradata/tt_02.dbf' logfile=imp_trans_tablespaces.log


3. Verify Tablespace and Object Movement

conn test/test

SELECT segment_name, tablespace_name from user_segments;

SEGMENT_NAME         TABLESPACE_NAME
-------------------- --------------------
TEST_DATA            TT01
PK_TEST_DATA_ID      TT02

SELECT * FROM TEST_DATA;

        ID ALPHABATE
---------- ----------
         1 A
         2 B
         3 C
         4 D
         5 E

Well we are done! To know more about TTS please go through the Oracle Doc http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces013.htm