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
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; 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
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 |