Transport Tablespace to Another Database

Step 1: Determine if Platforms are Supported and Endianness

Just Ignore his step if you are transporting the tablespace set between same platform.

SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

Step 2: Pick a Self-Contained Set of Tablespaces

You can ignore this step if we are sure that tablespaces we import has no dependency to other tablespace we omit. In this context "self-contained" means that there are no references from inside the set of tablespaces pointing outside of the tablespaces. Such as,

An index inside the set of tablespaces is for a table outside of the set of tablespaces.
A partitioned table is partially contained in the set of tablespaces.
A referential integrity constraint points to a table across a set boundary.
A table inside the set of tablespaces contains a LOB column that points to LOBs outside the set of
tablespaces.

EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('data01,data02,indx01,data03,COLUMBIA01,DATA02_16K_TBS', TRUE);

After invoking this PL/SQL package, you can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS view. If the set of tablespaces is self-contained, this view is empty.

SELECT * FROM TRANSPORT_SET_VIOLATIONS;

Step 3: Generate a Transportable Tablespace Set

1.Make all tablespaces in the set you are copying read-only.

ALTER TABLESPACE DATA01 READ ONLY;
and …..

2.Invoke the Data Pump export utility on the host system and specify which tablespaces are in the transportable set.

EXPDP system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir
TRANSPORT_TABLESPACES = data01,data02,indx01,data03,COLUMBIA01,DATA02_16K_TBS

Step 4: Transport the Tablespace Set

Transport both the datafiles and the export file of the tablespaces to a place that is accessible to the target database.We can use just scp command.
Step 5: Import the Tablespace Set

1.Import the tablespace metadata using the Data Pump Import utility, impdp:

IMPDP system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir
TRANSPORT_DATAFILES=
/oradata2/1_101.dbf,/oradata2/2_201.dbf

We can use parameter file if lots of data files are here.

Like,

IMPDP system/password PARFILE='par.f'

where the parameter file, par.f contains the following:

DIRECTORY=dpump_dir
DUMPFILE=expdat.dmp
TRANSPORT_DATAFILES="'/oradata2/1_101.dbf','/oradata2/2.dbf'"

2.Put the tablespaces into read/write mode as follows:

ALTER TABLESPACE Data01 READ WRITE;

and ….

Script

set heading off
set verify off
set feedback off
set echo off

spool tablespace_readonly.sql
select 'alter tablespace ' || tablespace_name || ' read only ;' column_tablespace from dba_tablespaces where
tablespace_name not
in ('SYSTEM', 'SYSAUX','UNDOTBS1','TEMP');
/
spool off

set heading on
set verify on
set feedback on
set echo on

@tablespace_readonly.sql

create directory trans as '/oradata2';
EXPDP system/sistem directory=trans dumpfile=meta_data.dmp TRANSPORT_TABLESPACES =
data01,data02,indx01,data03,COLUMBIA01,DATA02_16K_TBS

select 'scp ' || file_name || ' oracle@&hostname:&pathname ' from dba_data_files where tablespace_name not
in ('SYSTEM', 'SYSAUX','UNDOTBS1','TEMP');

In destination,

create a par_imp.f and input it by,

DIRECTORY=dpump_dir
DUMPFILE=meta_data.dmp
TRANSPORT_DATAFILES="'/oradata2/1_101.dbf','/oradata2/2.dbf'"

Now invoke,

IMPDP system/sistem parfile=par_imp.f