Cross -Platform Transportable Tablespace.

Transportable Tablespace

Transportable Tablespace feature was first introduced with Oracle database release 8i. This feature is used to copy a set of tablespaces from one Oracle database to another. It can be used to transport tablespaces across different database platforms as well.

Transporting Tablespace 

There are 4 steps to transport a tablespace

• Grant the necessary privileges
• Make the tablespace is transportable
• Generate the transportable tablespace set (data dictionary information)
• Copy the data files to the target server
• Perform the tablespace import
• Steps to Transporting Tablespace.

Source Machine

• First Create Tablespace  and User.Grant role to that user.

  Sql > Create tablespace onemb datafile           'c:\app\admin\oradata\orcl\onemb01.dbf' size 1m;

  Sql > Create user test identified by test default tablespace onemb quota unlimited on tablespace onemb temporary tablespace temp;

 Sql > grant connect, resource to test;

 hosted with ❤ by GitHub

• Check tablespace is ready to transport or not.when Tablespace object like (Table and Index) are stored in one tablespace these tablespace is ready to transport but these tablespace object are stored on other tablespace these tablespace is not ready to transport.
• Check Tablespace is transportable or not using.

  Sql > Execute dbms_tts.transport_set_check('ONEMB',true,TRUE);

 hosted with ❤ by GitHub

• Check which tablespace object is not in onemb tablespace using.

  Sql > Select * from transport_set_violations;

 hosted with ❤ by GitHub

• If object is found these object are stored on other tablespace. so first the object are transfer to 'ONEMB' Tablespace. else no obj found.

  Sql > alter tablespace ONEMB read only;

create directory dump c:\dump\
  Sql > create directory dump as 'c:\dump\';

 hosted with ❤ by GitHub

• Transport tablespace using Expdp

expdp system/admin directory=data_pump_dir dumpfile=transport_onemb.dmp transport_tablespaces= OBEMB

Or

expdp system/admin directory=data_pump_dir dumpfile=transport_onemb.dmp TRANSPORT_TABLESPACES= OBEMB TRANSPORT_FULL_CHECK=Y 

 hosted with ❤ by GitHub

• For Cross Platform Transportable Tablespace.

  Sql > Select t.endian_format from v$Transportable_platform t ,                v$database d where t.platform_name = d.platform_name;

 hosted with ❤ by GitHub

• I want to convert windows 64 bit to 'Linux IA (64-bit)'

rman target /

rman > convert tablespace 'ONEMB'

       to platform 'Linux IA (64-bit)'

       format 'c:\dump\onemb.dbf';

or

rman > convert tablespace 'ONEMB'

       to platform 'Linux IA (64-bit)'

       DB_FILE_NAME_CONVERT =            c:\app\admin\oradata\orcl\onemb.dbf','c:\dump\onemb.dbf';

 hosted with ❤ by GitHub

1. Target Machine 

• First Create user test.
• Grant connect,resource to test.
• Copy Export dump file transport_onemb.dmp and transfer to Target machine directory folder. and copy converted tablespace and copy to target machine.
• Then Import transport_onemb.dmp 
• after import check;
• select file_name,tablespace_name,file_name from dba_data_files;

• Impdp system/admin directory=data_pump_dir dumpfile=
• Follow all these steps for transportable tablespace.