Copy schema between databases through transportable tablespace

Today, I worked out a plan to copy a schema from one database to another using transportable tablespace feature.The schema objects only reside in three tablespaces. Below are the steps: (as usual, real schema, server name etc are modified):

1. Check if the tablespace is self-contained

conn / as sysdba
execute dbms_tts.transport_set_check('TST_PCD_DATA_1,TST_PCD_DATA_2,TST_PCD_IDX_1', true);

SELECT * FROM TRANSPORT_SET_VIOLATIONS;

Note: we should see no rows returned.


2. Make the tablespace READ ONLY at source:

alter tablespace TST_PCD_DATA_1 read only;
alter tablespace TST_PCD_DATA_2 read only;
alter tablespace TST_PCD_IDX_1 read only;

select tablespace_name, status from dba_tablespaces where status='READ ONLY';


4. Export the tablespace

exp tablespaces=TST_PCD_DATA_1,TST_PCD_DATA_2,TST_PCD_IDX_1 transport_tablespace=y file=sch1_tts.dmp

Username: / as sysdba


5. Drop the tablespaces in the target database

drop tablespace TST_PCD_DATA_1 including contents and datafiles;
drop tablespace TST_PCD_DATA_2 including contents and datafiles;
drop tablespace TST_PCD_IDX_1 including contents and datafiles;


6. Copy the data files and dump file to target server

select file_name from dba_data_files
where tablespace_name in ('TST_PCD_DATA_1','TST_PCD_DATA_2','TST_PCD_IDX_1');

FILE_NAME
--------------------------------------------------------------------------------
/db1/u01/oradata/sch1stg1/TST_PCD_IDX_1_01.dbf
/db1/u01/oradata/sch1stg1/TST_PCD_DATA_2_01.dbf
/db1/u01/oradata/sch1stg1/TST_PCD_DATA_1_01.dbf

ssh2 srcserv 'cat /db1/u01/oradata/sch1stg1/TST_PCD_IDX_1_01.dbf | gzip -c' | gunzip -c > /db1st2/u07/oradata/trgtdbsid/TST_PCD_IDX_1_01.dbf
ssh2 srcserv 'cat /db1/u01/oradata/sch1stg1/TST_PCD_DATA_2_01.dbf | gzip -c' | gunzip -c > /db1st2/u07/oradata/trgtdbsid/TST_PCD_DATA_2_01.dbf
ssh2 srcserv 'cat /db1/u01/oradata/sch1stg1/TST_PCD_DATA_1_01.dbf | gzip -c' | gunzip -c > /db1st2/u07/oradata/trgtdbsid/TST_PCD_DATA_1_01.dbf



7. Make the tablespace read write at source database

alter tablespace TST_PCD_DATA_1 read write;
alter tablespace TST_PCD_DATA_2 read write;
alter tablespace TST_PCD_IDX_1 read write;

8. At the target host, plug the tablespace into the target database.
From the OS command prompt, issue:

imp parfile=imp_tts.par

-- imp_tts.par --------------------------------------------
tablespaces=TST_PCD_DATA_1,TST_PCD_DATA_2,TST_PCD_IDX_1
transport_tablespace=y
file=sch1_tts.dmp
datafiles=(
'/db1st2/u07/oradata/trgtdbsid/TST_PCD_IDX_1_01.dbf'
,'/db1st2/u07/oradata/trgtdbsid/TST_PCD_DATA_2_01.dbf'
,'/db1st2/u07/oradata/trgtdbsid/TST_PCD_DATA_1_01.dbf'
)
tts_owners=ssp2_sch1
------------------------------------------------------------

Note: enter / as sysdba for username