Transportable tablespace
Oracle Database Cross Platform Transportable Tablespace
Source
More info in bulgarian
Oracle's transportable tablespace feature allows users to quickly move a user tablespace across Oracle databases. It is the most efficient way to move bulk data between databases.
Moving data using transportable tablespaces can be much faster than performing either an export/import or unload/load of the same data. This is because transporting a tablespace only requires the copying of datafiles and integrating the tablespace structural information.nbsp; You can also use transportable tablespaces to move both table and index data, thereby avoiding the index rebuilds you would have to perform when importing or loading table data.
The transportable tablespace feature is useful in a number of scenarios, including:
Exporting and importing partitions in data warehousing tables
Publishing structured data on CDs
Copying multiple read-only versions of a tablespace on multiple databases
Archiving historical data
Performing tablespace point-in-time-recovery (TSPITR)
Prior to Oracle Database 10g, if you want to transport a tablespace, both source and target databases need to be on the same platform.
Oracle Database 10g adds the cross platform support for transportable tablespaces. With the cross platform transportable tablespace, you can transport tablespaces across platforms. This functionality can be used to:
Provide an easier and more efficient means for content providers to publish structured data and distribute it to customers running Oracle on a different platform
Simplify the distribution of data from a data warehouse environment to data marts which are often running on smaller systems on different platforms
Enable the sharing of read only tablespaces across a heterogeneous cluster (the nodes must have same endianess)
Allow a database to be migrated from one platform to another (use with Data Pump or Import/Export)
Figure 1 shows the supported platforms for cross-platform tablespace transport. You can also query the V$TRANSPORTABLE_PLATFORM view to see the platforms that are supported, and to determine their platform IDs and their endian format (byte ordering).
If the source platform and the target platform are of different endianness, then an additional conversion step must be done on either the source or target platform to convert the tablespace being transported to the target format. If they are of the same endianness, then no conversion is necessary and tablespaces can be transported as if they were on the same platform.
Before a tablespace can be transported to a different platform, it must have been read/write a least once in an Oracle Database 10g with compatibility set to 10.0.0 or higher. This is because that action makes the datafiles within the tablespace platform aware, meaning that each file identifies the platform to which it belongs.
How the Transportable Tablespace Works
Figure 2 shows the general procedure of how you can transport a tablespace from on database to another. See the Database Documentation: Administrator's Guide, Transporting Tablespaces Between Databases section, for detailed steps and example.
How the Cross Platform Transportable Tablespace Works
Figure 3 shows the procedure of how you can transport a tablespace from one platform to another. See the Oracle Database 10g Documentation: Administrator's Guide, Transporting Tablespaces Between Databases section, for detailed steps and example.
Transportable Tablespace Considerations
As a rule, you can transport all objects or data types in the user tablespace, but there are some constraints and limitations:
The source and target database must use the same character set and national character set.
You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the tablespace to be transported or the destination tablespace before the transport operation.
New data types – Binary_Float and Binary_Double. These new Oracle10g data types are only supported by Data Pump. Original Export will not handle them.
Types opaque to Oracle. For example, RAW, BLOB, SYS.ANYTYPE, SYS.ANYDATA, etc. Interpretations of these data are application-specific and thus may affect cross-platform transportability. Note the potential incompatibility can even occur when transporting between platforms with same endianness. Be aware that any types or objects that use these types directly or indirectly also inherit the limitation. For example, AQ payloads can use RAW type and their transportability needs be verified, not assumed.
User-defined function, user-defined operator, etc. Oracle allows these definitions to appear in the following forms: SQL, PL/SQL, packages, C callouts. Defining in SQL is always transportable. PL/SQL, packages and C callouts are external to tablespaces. They need to be migrated or ported, in case of C callouts, in addition to the tablespace transport.
System, undo, sysaux, and temp tables cannot be transported.
If Automatic Storage Management (ASM) is used with either the source or destination database, you must use RMAN to transport/convert the tablespace.
Containment Violations
A containment violation occurs when a referenced object is not stored in the transportable set. The violations are usually caused by dependent or interdependent objects residing in different tablespaces. The following lists some of the object relationships that may cause containment violations:
A table and its index
An IOT and its overflow segment
Table partitions and subpartitions
A join index
A scoped REF
A referential integrity
A materialized view
These violations can be resolved by one of the following ways:
Including the required tablespaces to the transportable set. SYSTEM tablespace cannot be included in the transportable set
Move the uncontained objects into the transportable set
Move the violating objects out of the transportable set
Dropping the violating objects and/or the relationship
Objects outside of the User Tablespace
If the user or application requires objects outside of the user tablespace that is being transported, additional actions may be required.
System tablespace objects. Transportable tablespace feature does not allow the transport of the SYSTEM tablespace. If there are user objects in SYSTEM tablespace (not a recommended practice) to be transported, they should be moved to a user tablespace or transported with export/import.
On the other hand, certain database objects are stored in the SYSTEM tablespace. For example, database links, synonyms, roles, privileges, dimensions, etc. This information may be a prerequisite to import the user tablespaces correctly. A user must prepare the recreation script manually or export a SYSTEM schema.
Objects owned by SYS should not reside in a user tablespace. They must be moved out of the user tablespaces before the user tablespaces can be transported.
SYS owned objects (packages, views, java classes, sequences, etc.) are not transported by transportable tablespaces. A user must make sure one of the following is done:
Target database installation and creation will prepare these SYS owned objects correctly
These SYS owned objects are exported from source database
External objects (parameter files, external tables, bfiles, etc.) are external to tablespaces. They need to be prepared and migrated in addition to tablespace transport.
Source
More info in bulgarian
Oracle's transportable tablespace feature allows users to quickly move a user tablespace across Oracle databases. It is the most efficient way to move bulk data between databases.
Moving data using transportable tablespaces can be much faster than performing either an export/import or unload/load of the same data. This is because transporting a tablespace only requires the copying of datafiles and integrating the tablespace structural information.nbsp; You can also use transportable tablespaces to move both table and index data, thereby avoiding the index rebuilds you would have to perform when importing or loading table data.
The transportable tablespace feature is useful in a number of scenarios, including:
Exporting and importing partitions in data warehousing tables
Publishing structured data on CDs
Copying multiple read-only versions of a tablespace on multiple databases
Archiving historical data
Performing tablespace point-in-time-recovery (TSPITR)
Prior to Oracle Database 10g, if you want to transport a tablespace, both source and target databases need to be on the same platform.
Oracle Database 10g adds the cross platform support for transportable tablespaces. With the cross platform transportable tablespace, you can transport tablespaces across platforms. This functionality can be used to:
Provide an easier and more efficient means for content providers to publish structured data and distribute it to customers running Oracle on a different platform
Simplify the distribution of data from a data warehouse environment to data marts which are often running on smaller systems on different platforms
Enable the sharing of read only tablespaces across a heterogeneous cluster (the nodes must have same endianess)
Allow a database to be migrated from one platform to another (use with Data Pump or Import/Export)
Figure 1 shows the supported platforms for cross-platform tablespace transport. You can also query the V$TRANSPORTABLE_PLATFORM view to see the platforms that are supported, and to determine their platform IDs and their endian format (byte ordering).
If the source platform and the target platform are of different endianness, then an additional conversion step must be done on either the source or target platform to convert the tablespace being transported to the target format. If they are of the same endianness, then no conversion is necessary and tablespaces can be transported as if they were on the same platform.
Before a tablespace can be transported to a different platform, it must have been read/write a least once in an Oracle Database 10g with compatibility set to 10.0.0 or higher. This is because that action makes the datafiles within the tablespace platform aware, meaning that each file identifies the platform to which it belongs.
How the Transportable Tablespace Works
Figure 2 shows the general procedure of how you can transport a tablespace from on database to another. See the Database Documentation: Administrator's Guide, Transporting Tablespaces Between Databases section, for detailed steps and example.
How the Cross Platform Transportable Tablespace Works
Figure 3 shows the procedure of how you can transport a tablespace from one platform to another. See the Oracle Database 10g Documentation: Administrator's Guide, Transporting Tablespaces Between Databases section, for detailed steps and example.
Transportable Tablespace Considerations
As a rule, you can transport all objects or data types in the user tablespace, but there are some constraints and limitations:
The source and target database must use the same character set and national character set.
You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the tablespace to be transported or the destination tablespace before the transport operation.
New data types – Binary_Float and Binary_Double. These new Oracle10g data types are only supported by Data Pump. Original Export will not handle them.
Types opaque to Oracle. For example, RAW, BLOB, SYS.ANYTYPE, SYS.ANYDATA, etc. Interpretations of these data are application-specific and thus may affect cross-platform transportability. Note the potential incompatibility can even occur when transporting between platforms with same endianness. Be aware that any types or objects that use these types directly or indirectly also inherit the limitation. For example, AQ payloads can use RAW type and their transportability needs be verified, not assumed.
User-defined function, user-defined operator, etc. Oracle allows these definitions to appear in the following forms: SQL, PL/SQL, packages, C callouts. Defining in SQL is always transportable. PL/SQL, packages and C callouts are external to tablespaces. They need to be migrated or ported, in case of C callouts, in addition to the tablespace transport.
System, undo, sysaux, and temp tables cannot be transported.
If Automatic Storage Management (ASM) is used with either the source or destination database, you must use RMAN to transport/convert the tablespace.
Containment Violations
A containment violation occurs when a referenced object is not stored in the transportable set. The violations are usually caused by dependent or interdependent objects residing in different tablespaces. The following lists some of the object relationships that may cause containment violations:
A table and its index
An IOT and its overflow segment
Table partitions and subpartitions
A join index
A scoped REF
A referential integrity
A materialized view
These violations can be resolved by one of the following ways:
Including the required tablespaces to the transportable set. SYSTEM tablespace cannot be included in the transportable set
Move the uncontained objects into the transportable set
Move the violating objects out of the transportable set
Dropping the violating objects and/or the relationship
Objects outside of the User Tablespace
If the user or application requires objects outside of the user tablespace that is being transported, additional actions may be required.
System tablespace objects. Transportable tablespace feature does not allow the transport of the SYSTEM tablespace. If there are user objects in SYSTEM tablespace (not a recommended practice) to be transported, they should be moved to a user tablespace or transported with export/import.
On the other hand, certain database objects are stored in the SYSTEM tablespace. For example, database links, synonyms, roles, privileges, dimensions, etc. This information may be a prerequisite to import the user tablespaces correctly. A user must prepare the recreation script manually or export a SYSTEM schema.
Objects owned by SYS should not reside in a user tablespace. They must be moved out of the user tablespaces before the user tablespaces can be transported.
SYS owned objects (packages, views, java classes, sequences, etc.) are not transported by transportable tablespaces. A user must make sure one of the following is done:
Target database installation and creation will prepare these SYS owned objects correctly
These SYS owned objects are exported from source database
External objects (parameter files, external tables, bfiles, etc.) are external to tablespaces. They need to be prepared and migrated in addition to tablespace transport.
Comments