Transportable Tablespaces

You can move large amounts of data between databases just simply by moving data files from one database to another. You copy all the data files from the source database to the target database and import the data dictionary information about the tablespaces from the source database to the target database.

You use transportable tablespaces mainly in the context of a data warehouse, some of the important features are

Transporting a tablespace

There are 4 steps to transport a tablespace

  1. Grant the necessary privileges
  2. Make the tablespace is transportable
  3. Generate the transportable tablespace set (data dictionary information)
  4. Copy the data files to the target server
  5. Perform the tablespace import
Privilege required to check tablespace grant EXECUTE_CATALOG_ROLE to vallep;
Make sure a tablespace is transportable

execute dbms_tts.transport_set_check('test01, test02', true);

select * from transport_set_violation;

Note: if there are any errors then check with Oracle to see how to get around them

Generate the transportable tablespace set

alter tablespace test01 read only;
alter tablespace test02 read only;

expdp vallep/vallep directory=data_pump_dir dumpfile=test.dmp transport_tablespace=test01,test02 include=triggers,constraints,grant

Note: The tablespaces must be in read only mode and only metadata (data dictionary data) will be contained in the data pump export
Copy the Data to target server Now copy all the data files and the data pump export to the target server
Tablespace import impdp system/system dumpfile=test.dmp transport_datafiles='test01.dbf','test02.dbf' directory=data_pump_dir

Transporting tablespaces on different platforms

You can transport tablespace between different server architectures, there is only one requirement both platform must have the same endian format. endian format refers to the byte ordering of the file system, they can be one of two types big or small, if they differ you must convert the data files to the format you require.

To check what format you have

Check endian format select t.endian_format from v$transportable_platform t, v$database d
where t.platform_name = d.platform_name;

The steps to transport a tablespace that are a different endian format are

  1. Ensure that the tablespaces are self-contained
  2. Make the tablespaces read-only
  3. Export the metadata using Data Pump Export
  4. Convert the data files to match the endian format
  5. Copy the converted data files to the target system
  6. Use the Data Pump Import utility to import the metadata
Source Server
Privilege required to check tablespace grant EXECUTE_CATALOG_ROLE to vallep;
Make sure a tablespace is transportable

execute dbms_tts.transport_set_check('test01, test02', true);

select * from transport_set_violation;

Note: if there are any errors then check with Oracle to see how to get around them

make the tablespace read only alter tablespace test01 read only;
alter tablespace test02 read only;
Generate the transportable tablespace set

expdp vallep/vallep directory=data_pump_dir dumpfile=test.dmp transport_tablespace=test01,test02 include=triggers,constraints,grant

Note: remember we are only exporting the metadata
Convert the tablespace on the source server (this can also be done on the target server)
convert the data files (using CONVERT)

rman> convert tablespace test01 to platform 'HP-UX (64bit)' format '/temp/%U';

Note: Oracle will tell you the new file name of the converted file

convert the data files (using DB_FILE_NAME_CONVERT rman> convert tablespace test01 to platform 'HP-UX (64-bit)'
db_file_name _convert = 'c:\oracle\test01.dbf','c:\convert\test01.dbf';
Copy the Data Now copy all the data files and the data pump export to the target server if not already copied
Target Server
Tablespace import impdp system/system dumpfile=test.dmp transport_datafiles='test01.dbf','test02.dbf' directory=data_pump_dir