DATAPUMP UTILITY:-Transportable Tablespace using move tablespace ond db to another db

           This article discusses simple example of performing TTS (Transportable Tablespace) across two databases running one same OS and using same database version.

SOURCE DB
1.Lets create tablespace and user TTS for this test

SQL>create tablespace tts_test01 datafile '/u01/oradata/orc01d/tts_test01_01.dbf' size 256m autoextend on next 256m maxsize 20g;
SQL>create user TTS identified by TTS_123 default tablespace tts_test01;
SQL>grant connect,resource to tts;

2.We wish to transport a table EMP_STORE with CLOB columns to our new database

sys@orcl01d>grant select on scott.EMP_STORE to tts;
sys@orcl01d>conn tts/tts_123
tts@orcl01d>create table EMP_STORE_TTS as select * from scott.EMP_STORE;
Table created.

3.Lets verify the objects in the tablespace

sys@orcl01d>select owner,SEGMENT_NAME,SEGMENT_TYPE from dba_segments where tablespace_name='TTS_TEST01'
OWNER SEGMENT_NAME SEGMENT_TYPE
---------- ------------------------------ ------------------
TTS EMP_STORE_TTS TABLE
TTS SYS_IL0000333770C00003$$ LOBINDEX
TTS SYS_LOB0000333770C00003$$ LOBSEGMENT

4.We need to check if this tablespace is self contained and satisfies pre-requisites for TTS. We execute DBMS_TTS.TRANSPORT_SET_CHECK with tablespace_name as argument

sys@orcl01d> exec DBMS_TTS.TRANSPORT_SET_CHECK('TTS_TEST01')
PL/SQL procedure successfully completed.
sys@orcl01d> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected

note:-they will show the the possible error that may rise if the tablespace is not compatible or ready to transpoted

5.Above query does not return any rows, so we are safe to proceed.
                Once done, we need to make the tablespace read-only

sys@orcl01d>alter tablespace TTS_TEST01 read only;

6.Create oracle directory to store dumpfile. 
Note we are using exclude=xmlschema to avoid Unpublished Bug 10185688 (Data Pump TTS Export Fails With ORA-39126 & ORA-904 [ID 1273450.1] ) 
which causes following errors

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA [PLUGTS_BLK]
ORA-00904: "Q"."TABOBJ_NUM": invalid identifier
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 8358

7.We will use expdp to perform export and will be using TRANSPORT_TABLESPACES option.

[oracle@orcl01d]~% expdp system DUMPFILE=tts.dmp DIRECTORY=EMP_test TRANSPORT_TABLESPACES=TTS_TEST01
logfile=tts_exp.log exclude=xmlschema


Export: Release 11.2.0.2.0 - Production on Thu Jul 26 01:47:58 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** DUMPFILE=tts.dmp DIRECTORY=EMP_test
TRANSPORT_TABLESPACES=TTS_TEST01 logfile=tts_exp.log exclude=xmlschema
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
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:
/home/oracle/EMP/tts.dmp
******************************************************************************
Datafiles required for transportable tablespace TTS_TEST01:
/u01/oradata/orc01d/tts_test01_01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 01:48:42

8.Copy the datafile(USING SCP OR CP /u01/oradata/orc01d/tts_test01_01.dbf /u04/oradata/orc02d/tts_test01_01.dbf) to 
the new system and copy to database directory. Once done place tablespace back in read-write mode

sys@orcl01d>alter tablespace TTS_TEST01 read write;

TARGET DB
1.Create the database user and assign appropriate privileges. Note that TTS only imports table,indexes and associated triggers. You need to have pre-created schema

create user TTS identified by TTS_123 default tablespace tts_test02;
grant connect,resource to tts;

2.Now import the datafile and give the location using impdp parameter TRANSPORT_DATAFILES

[oracle@orcl02d]~% impdp system DUMPFILE=tts.dmp DIRECTORY=EMP_test
TRANSPORT_DATAFILES=/u04/oradata/orc02d/tts_test01_01.dbf logfile=tts_imp.log

Import: Release 11.2.0.2.0 - Production on Thu Jul 26 02:34:29 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** DUMPFILE=tts.dmp DIRECTORY=EMP_test
TRANSPORT_DATAFILES=/u04/oradata/orc02d/tts_test01_01.dbf logfile=tts_imp.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 02:34:42

3.Lets verify whether object is imported

sys@orcl02d>select owner,SEGMENT_NAME,SEGMENT_TYPE from dba_segments where tablespace_name='TTS_TEST01'

OWNER SEGMENT_NAME SEGMENT_TYPE
---------- ------------------------------ ------------------
TTS EMP_STORE_TTS TABLE
TTS SYS_IL0004878485C00003$$ LOBINDEX
TTS SYS_LOB0004878485C00003$$ LOBSEGMENT
Query to ensure records are present

sys@orcl02d> select count(*) from TTS.EMP_STORE_TTS;
COUNT(*)
----------
163856

4.Tablespace is imported in read-only mode which can be verified by querying status column in dba_tablespaces

sys@orcl02d> select tablespace_name,status from dba_tablespaces where tablespace_name='TTS_TEST01';

TABLESPACE_NAME STATUS
------------------------------ ---------
TTS_TEST01 READ ONLY

Lets make it read-write as we want users to be able to update table
sys@orcl02d> alter tablespace TTS_TEST01 read write;
Tablespace altered.

You will notice that this is really fast approach of copying objects across tablespace as most of time taken isfor copying the datafile to target system.You will notice that we had kept tablespace in read-only mode while taking export and copying files.If you wish toworkaround this problem (as this causes downtime for any dml operation), you will have to make use of RMAN to perform TTS.