Oracle transportable tablespaces - DBA

Introduction to transportable tablespaces

You can use the TRANSPORTABLE TABLESPACES feature to copy a set of tablespaces from one oracle database to another.

Note:

This method for transporting tablespaces requires that you place the tablespaces to be transported in read-only mode until you complete the transporting process. If this is undesirable, you can use the transportable tablespaces from backup feature.

The tablespaces being transported can be either dictionary managed or locally managed. Starting with oracle9i, the transported tablespaces are not required to be of the same block size as the target database standard block size.

Moving data using transportable tablespaces is much faster than performing either an export/import or unload/load of the same data. This is because the datafiles containing all of the actual data are just copied to the destination location, and you use an export/import utility to transfer only the metadata of the tablespace objects to the new database.

Note:

The transportable tablespaces feature supports both data pump and the original import and export utilities, imp and exp, with one caveat: you must use imp and exp for tablespaces containing xmltypes.

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 table space point-in-time-recovery (tspitr)

There are two ways to transport a tablespace:

• Manually, following the steps described in this section. This involves issuing commands to sql*plus, rman, imp/exp and data pump.
• Using the transport tablespaces wizard in enterprise manager

To run the transport tablespaces wizard:

1. Log in to enterprise manager with a user that has the exp_full_database
2. Click the maintenance link to go to the maintenance tab.
3. Under the heading move database files, click transport tablespaces.

About transporting tablespaces across platforms:

Starting with oracle database 10g, you can transport tablespaces across platforms. This functionality can be used to:

• Allow a database to be migrated from one platform to another
• Provide an easier and more efficient means for content providers to publish structured data and distribute it to customers running oracle database on different platforms
• Simplify the distribution of data from a data warehouse environment to data marts, which are often running on smaller platforms
• Enable the sharing of read-only tablespaces between oracle database installations on different operating systems or platforms, assuming that your storage system is accessible from those platforms and the platforms all have the same endianness.

Many, but not all, platforms are supported for cross-platform tablespace transport. You can query the v$transportable_platform view to see the platforms that are supported, and to determine each platform’s endian format (byte ordering). The following query displays the platforms that support cross-platform tablespace transport:

Sql> column platform_name format a32
Sql> select * from v$transportable_platform;

Platform_id              platform_name                                  endian_format

          1                   solaris[tm] oe (32-bit)                             big

          2                   solaris[tm] oe (64-bit)                             big

          7                   microsoft windows ia (32-bit)                little

         10                  linux ia (32-bit)                                          little

          6                   aix-based systems (64-bit)                    big

          3                   hp-ux (64-bit)                                           big

          5                   hp tru64 unix                                          little

          4                   hp-ux ia (64-bit)                                      big

         11                  linux ia (64-bit)                                       little

         15                  hp open vms                                         little

          8                   microsoft windows ia (64-bit)          little

          9                   ibm zseries based linux                       big

         13                  linux 64-bit for amd                             little

         16                  apple mac os                                         big

         12                  microsoft windows 64-bit for amd       little

         17                  solaris operating system (x86)             little

16 rows selected.

If the source platform and the target platform are of different endianness, then an additional 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, the datafile header must identify the platform to which it belongs. In an oracle database with compatibility set to 10.0.0 or later, you can accomplish this by making the datafile read/write at least once.

Checkout Oracle DBA Interview Questions

Limitations on transportable tablespace use:

Be aware of the following limitations as you plan to transport tablespaces:

• 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.
• Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects are in the tablespace set.
• Beginning with oracle database 10g release 2, you can transport tablespaces that contain xmltypes, but you must use the imp and exp utilities, not data pump. When using exp, ensure that the constraints and triggers parameters are set to y (the default).

The following query returns a list of tablespaces that contain xmltypes:

Select distinct p.tablespace_name from dba_tablespaces p, 
 Dba_xml_tables x, dba_users u, all_all_tables t 
 where T.table_name=x.table_name and t.tablespace_name=p.tablespace_name And x.owner=u.username

Transporting tablespaces with xmltypes has the following limitations:

• The target database must have xml db installed.
• Schemas referenced by xmltype tables cannot be the xml db standard schemas.
• Schemas referenced by xmltype tables cannot have cyclic dependencies.
• Any row level security on xmltype tables is lost upon import. This is because the access control lists (acls) that implement the row level security cannot be imported, as the target database may not have the same set of users as the source database.
• If the schema for a transported xmltype table is not present in the target database, it is imported and registered. If the schema already exists in the target database, an error is returned unless the ignore=y option is set.

Additional limitations include the following:

Advanced queues:

Transportable tablespaces do not support 8.0-compatible advanced queues with multiple recipients.

System tablespace objects :

You cannot transport the system tablespace or objects owned by the user sys. Some examples of such objects are pl/sql, java classes, callouts, views, synonyms, users, privileges, dimensions, directories, and sequences.

Opaque types:

Types whose interpretation is application-specific and opaque to the database (such as raw, bfile, and the anytypes) can be transported, but they are not converted as part of the cross-platform transport operation. Their actual structure is known only to the application, so the application must address any endianness issues after these types are moved to the new platform. Types and objects that use these opaque types, either directly or indirectly, are also subject to this limitation.

Floating-point numbers:

Binary_float and binary_double types are transportable using data pump but not the original export utility, exp.

Compatibility considerations for transportable tablespaces:

When you create a transportable tablespace set, oracle database computes the lowest compatibility level at which the target database must run. This is referred to as the compatibility level of the transportable set. Beginning with oracle database 10g, a tablespace can always be transported to a database with the same or higher compatibility setting, whether the target database is on the same or a different platform. The database signals an error if the compatibility level of the transportable set is higher than the compatibility level of the target database.

The following table shows the minimum compatibility requirements of the source and target tablespace in various scenarios. The source and target database need not have the same compatibility setting.

Table minimum compatibility requirements