The transportable tablespace feature allows you to move or copy a subset of an Oracle database from one Oracle database to another. Transportable tablespace aids in any situations requiring bulk data to be transferred such as during the following scenarios.
- We can use for archival of pertinent data prior to purging it without significant impact to the source database.
- Migration of the source data from an OLTP to a reporting database of transfer of data from operational data sources to the data warehousing.
- Transfer of production data to a test platform.
- We can copy data from the enterprise data warehouse to temporary staging databases for massaging or converting data prior to loading down-stream data marts.
Moving data via transportable tablespace is much faster than conventional upload/load utilities, such as export/import and SQL*Loader. The reason for this is that, transporting a tablespace only requires the data files to be copied across from the source to the target database and integrating metadata pertaining to the tablespace structure. Index data can also be copied or moved without them having to be rebuilt.
When a tablespace is to be transported, it is placed in read only mode to ensure that a consistent image of the data is captured. Then only specific dictionary information is exported from the source data dictionary. Next, the tablespace data files are copied across to the target database via any operating system utilities or commands. Then the metadata describing the tablespace is imported into the target database. This is very fast because the size of the import is minimized. Optionally, the transferred tablespace can then be placed in read write mode. The actual implementation includes using the datapump utility or export/import utilities. The exp utility has a option, transport_tablespace, whereas imp has three options, transport_tablespace, data files, and tts_owners, to support this feature.
You can transport tablespace between Oracle databases that use the same data block size and character set. Also the source and target platform should be compatible and, it possible, from the same hardware vendor. Also, to prevent violation of functional and physical dependencies and referential integrity, another limitation allows only called "self-contained" tablespace to be transported. "Self-contained" means that there should be no references within the tablespace pointing to segments outside the tablespace. For example, if the tablespace set you wish to copy must contain either all or none of the partitions of a partitioned table. The procedure DBMS_TTS.transport_set_check can be used to determine whether or not the tablespace in question is self-contained. All violations reported by this procedure can be seen within transport_set_violations view which will be empty when there are no violations. In order to determine which of the tablespace in the current database have been transported the plugged_in column in "dba_tablespaces" can be checked.
0 comments:
Post a Comment