Table Space Management   «Prev  Next»

Lesson 4 Transportable tablespaces
Objective Define transportable tablespaces.

Define Transportable Tablespaces

Describe the purpose and function of transportable tablespaces in Oracle 19c.
Transportable Tablespaces (TTS) in Oracle 19c are a feature designed to facilitate the rapid movement of large volumes of data between Oracle databases. This feature is particularly useful for applications like data warehousing, data migration, and consolidation projects. Below is a detailed description of the purpose and function of transportable tablespaces:
Purpose of Transportable Tablespaces
  1. Efficient Data Movement:
    • Transportable tablespaces allow you to move data between Oracle databases without the need for time-consuming export/import operations. Instead of copying individual rows, the entire tablespace data files are transferred.
  2. Cross-Platform Data Transfer:
    • TTS supports transferring tablespaces between databases running on different operating systems or hardware platforms, provided that the source and target platforms are compatible in terms of endian format.
  3. Data Consolidation:
    • Facilitates the consolidation of multiple databases by allowing tablespaces from separate databases to be added to a centralized database.
  4. Data Archival and Distribution:
    • Useful for creating archives or distributing static data, such as historical data or reference datasets, to other databases.
  5. Minimized Downtime:
    • Since TTS avoids the need to unload and reload data, it reduces downtime significantly during migrations or data movement.
  6. Data Sharing:
    • Enables sharing large read-only datasets, such as lookup tables or historical data, across different Oracle databases.

Functionality of Transportable Tablespaces
  1. Basic Concept:
    • The transportable tablespace feature moves tablespace data files and the associated metadata (stored as an export file) to a target database.
  2. Steps to Use Transportable Tablespaces:
    • Set the Tablespace to READ ONLY:
      Before transporting, the tablespace must be set to READ ONLY to ensure data consistency: 
      ALTER TABLESPACE tablespace_name READ ONLY;
    • Export Metadata:
      Use Oracle's Data Pump Export to extract metadata about the objects in the tablespace (e.g., table definitions, indexes): 
      EXPDP user/password DIRECTORY=dir_name DUMPFILE=export_file.dmp TRANSPORT_TABLESPACES=tablespace_name
    • Copy Data Files:
      Manually copy the data files associated with the tablespace to the target database server.
    • Import Metadata:
      Use Oracle's Data Pump Import to import the metadata into the target database: 
      IMPDP user/password DIRECTORY=dir_name DUMPFILE=export_file.dmp TRANSPORT_DATAFILES='/path/to/datafile'
    • Set the Tablespace to READ WRITE:
      Once imported, the tablespace can be set to READ WRITE in the target database: 
      ALTER TABLESPACE tablespace_name READ WRITE;
  3. Cross-Platform Compatibility:
    • Transportable tablespaces can be moved between platforms with the same or different endian formats.
    • If the endian formats differ, you must convert the data files using the RMAN CONVERT command: 
      RMAN> CONVERT DATAFILE '/source/path/datafile.dbf' 
        FROM PLATFORM 'Source Platform'
        FORMAT '/target/path/datafile.dbf';
  4. Metadata Validation:
    • Oracle validates the metadata during the import process to ensure that the tablespace structure and objects align with the target database schema.
  5. Support for Partitioned Tables:
    • You can transport specific partitions of a partitioned table by transporting the tablespace containing those partitions.

Advantages of Transportable Tablespaces
  1. Speed:
    • Moving data files is faster than exporting and importing row-by-row.
  2. Minimized Impact on Production:
    • Data remains accessible during the process since the tablespace is only temporarily set to READ ONLY.
  3. Reduced Disk I/O:
    • Bypasses the need to read and write every row during the migration process.
  4. Scalability:
    • Ideal for handling large databases and massive datasets efficiently.
  5. Flexibility:
    • Enables both same-platform and cross-platform data transfers.
  6. Cross-Version Compatibility:
    • Supports transport between databases of different versions, provided the target version is newer than or the same as the source version.

Common Use Cases for Transportable Tablespaces
  1. Data Migration:
    • Moving large data sets to a new database, such as during hardware or platform upgrades.
  2. Data Warehousing:
    • Transferring historical or aggregated data from OLTP systems to a data warehouse.
  3. Database Consolidation:
    • Consolidating multiple databases into a single database instance for better resource management.
  4. Archiving:
    • Archiving large tablespaces for historical or regulatory purposes.
  5. Distributing Read-Only Data:
    • Sharing static reference data or lookup tables across multiple databases or environments.

Limitations of Transportable Tablespaces
  1. Tablespace Constraints:
    • Only tablespaces that are in READ ONLY mode can be transported.
  2. Dependency Handling:
    • Dependencies on objects in other tablespaces (e.g., foreign keys) need to be resolved before transporting.
  3. Platform Compatibility:
    • Cross-platform transport requires matching or converting endian formats.
  4. Unsupported Objects:
    • Certain database objects like materialized views or external tables may not be transportable.
  5. Storage Considerations:
    • Sufficient storage must be available on the target database to accommodate the data files.

Conclusion Transportable Tablespaces in Oracle 19c are a robust and efficient way to move large datasets between Oracle databases while minimizing downtime and resource usage. They are particularly well-suited for data warehousing, migration, and sharing scenarios, making them an essential tool for modern database administrators.
When copying large chunks of data from one database to another prior to Oracle, you had to choose among several options, all of which were time-consuming and required you to create an intermediate file to hold the data. Oracle has introduced a new feature for copying or moving a set of tablespaces from one Oracle database to another, called the transportable tablespace. The transportable tablespace feature makes it as easy to move database data as it is to move a file from one location to another. Some of the uses for this feature are:
  1. Preparing data in one database and then copying the data to several distributed databases
  2. Gathering data into summarized historical tables, then moving the tablespace to your data warehouse

Oracle 19c supports Transporting and cloning of Tablespaces

Oracle 19c** supports both **transporting** and **cloning** of tablespaces. These features are part of Oracle's advanced data movement and management capabilities, designed to facilitate efficient database operations in a variety of scenarios.
Transporting Tablespaces in Oracle 19c** Transportable tablespaces allow you to move tablespaces (and the data within them) between Oracle databases, even across different platforms.
Key Features:**
  1. Cross-Platform Support:
    • Oracle 19c supports transportable tablespaces across platforms with compatible or different endian formats. If endian formats differ, the RMAN CONVERT command is used to convert the data files.
  2. Transporting Tablespaces with Data Pump:
    • Oracle 19c integrates with Oracle Data Pump for exporting and importing metadata during the tablespace transport process.
  3. Steps to Transport Tablespaces:
    • Set Tablespace to READ ONLY: 
      ALTER TABLESPACE tablespace_name READ ONLY;
    • Export Metadata with Data Pump: 
      expdp user/password DIRECTORY=dir_name DUMPFILE=export.dmp TRANSPORT_TABLESPACES=tablespace_name
    • Copy Data Files to Target:
      Transfer the tablespace data files to the target system.
    • Import Metadata at Target: 
      impdp user/password DIRECTORY=dir_name DUMPFILE=export.dmp TRANSPORT_DATAFILES='/path/to/datafile'
    • Set Tablespace to READ WRITE: 
      ALTER TABLESPACE tablespace_name READ WRITE;
  4. Use Cases:
    • Migrating data between databases.
    • Sharing static, read-only data across environments.
    • Archiving large volumes of data.

Cloning Tablespaces in Oracle 19c
Cloning tablespaces allows you to create a copy of a tablespace within the same or a different database.
Key Features:**
  1. Cloning with RMAN:
    • Oracle 19c allows you to use RMAN (Recovery Manager) to clone a tablespace. This feature is particularly useful for testing, development, or reporting purposes.
  2. Snapshot Copy:
    • Oracle 19c can create snapshot copies of tablespaces, enabling you to work with a point-in-time copy of the data.
  3. Steps to Clone Tablespaces:
    • Prepare Source Tablespace:
      Ensure the source tablespace is in a consistent state.
    • Use RMAN to Clone:
      Execute commands to duplicate the tablespace. 
      RMAN> DUPLICATE TABLESPACE tablespace_name TO NEW TABLESPACE new_tablespace_name;
    • Use Pluggable Databases (PDBs):
      In multitenant environments, cloning operations are simplified and can be executed within pluggable databases (PDBs).
  4. Online and Offline Cloning:
    • Oracle 19c supports online tablespace cloning, allowing the source tablespace to remain available during the cloning process.
    • Offline cloning requires taking the source tablespace offline before cloning.
  5. Use Cases:
    • Creating a test or development environment.
    • Creating backups for reporting and analysis.
    • Distributing data for parallel processing.
Comparison of Transporting vs. Cloning
Feature Transporting Tablespaces Cloning Tablespaces
Purpose Move data between databases. Create a duplicate tablespace within the same or another database.
Cross-Platform Supported with endian format conversion if needed. Typically for the same platform, unless using PDB cloning.
Online Support Requires READ ONLY mode for source tablespace. Can be done online (source remains available).
Dependency Handling Requires resolving dependencies before transport. No dependency issues if cloning within the same database.
Use Cases Data migration, archiving, sharing read-only data. Testing, development, reporting.

Conclusion Oracle 19c provides robust support for both transporting and cloning tablespaces. Transportable tablespaces are ideal for moving large datasets between databases or platforms, while tablespace cloning is perfect for creating local copies for testing or analysis. These features enhance Oracle's flexibility and scalability for managing large, complex databases.
The current release of Oracle restricts the transporting and cloning of tablespaces. You can only do this between computers with the same hardware, database block size, and database character set. This restriction will likely disappear in later releases as well.
Objects in the tablespaces that you wish to transport must not reference any object outside the tablespaces. The tablespaces must be totally self-contained. For example, let us say you select a tablespace that contains an index. The index is based on a table found in another tablespace. The initial tablespace cannot be transported by itself because it refers to something outside of itself. You could transport the two tablespaces as a set, however, assuming that the two only reference each other. Look at the following series of images below to see a demonstration of the steps for transporting a tablespace.

Oracle Transportable Tablespaces

1)First step in the process is to change the tablespace you wish to transport into READ ONLY status
1) First step in the process is to change the tablespace you wish to transport into READ ONLY status. This example shows the command to change the WAREHOUSE1 tablespace so that it is in READ ONLY status. If you are exporting a set of tablespaces, repeat this command for each one.

Analyze the following uploaded image. Print off the Oracle code and script in the image.
2) Second step in the process is to export the tablespace. This screen shows the syntax of the command you will use and the actual export command used in our example
2)
Parameter File Example 
EXP PARFILE='parameterfile'

Parameter file contents:

TRANSPORT_TABLESPACE=y
TABLESPACES=tablespace_name1, tablespace_name2
[TRIGGERS={y|n}] [CONSTRAINTS={y|n}] [GRANTS={y|n}]
FILE=filename

GO.TXT Example 
EXP PARFILE='GO.TXT'

Contents of GO.TXT:

EXP TRANSPORT_TABLESPACE=y
TABLESPACES=warehouse1
FILE=warehouse1.dmp
Second step in the process is to export the tablespace. This screen shows the syntax of the command you will use and the actual export command used in our example. You create a parameter file that contains all the details needed to export the file. The green highlighted area show optional parameters that can save exporting time if used with the 'n' option. For example, if you know that there are no grants you wish to export, specify GRANTS =n and the export process will skip this portion of its routine.


3) Screen shot of the command line and status feedback that was created when exporting the example tablespace.
3) Screen shot of the command line and status feedback that was created when exporting the example tablespace.

4) The next step is to copy the export file just created and all the data files for the tablespaces to a location that is accessible by the target database.
4) The next step is to copy the export file just created and all the data files for the tablespaces to a location that is accessible by the target database. This screen shows you how you might accomplish this in the Windows environment using Windows Explorer, but you use any operating system tool that you prefer for copying and pasting the files.

5) Now use the import utility (IMP) to import the meta-data into the target database. This screen shows the syntax and the exact command for our sample tablespace, WAREHOUSE1.
5) Now use the import utility (IMP) to import the meta-data into the target database. This screen shows the syntax and the exact command for our sample tablespace, WAREHOUSE1. The details are again placed into a parameter file that is named in the IMP command. The green highlight shows optional parameters for listing all the owners in the tablespace (TTSOWNERS) and listing how to substitute one owner for another (FROM USER and TOUSER) when doing the import process. When these are left out, the owners of all the object remain unchanged.

6) Here is a screen shot of the command line and status feedback that was created when importing the example tablespace
6) Here is a screen shot of the command line and status feedback that was created when importing the example tablespace.

7) You can now modify the tablespace so that it is in a READ WRITE state using the command show here.
7) You can now modify the tablespace so that it is in a READ WRITE state using the command show here.

  1. First step in the process is to change the tablespace you wish to transport into READ ONLY status.
  2. Second step in the process is to export the tablespace. This screen shows the syntax of the command you will use and the actual export command used in our example.
  3. Screen shot of the command line and status feedback that was created when exporting the example tablespace.
  4. The next step is to copy the export file just created and all the data files for the tablespaces to a location that is accessible by the target database
  5. Now use the import utility (IMP) to import the meta-data into the target database. This screen shows the syntax and the exact command for our sample tablespace, WAREHOUSE1.
  6. Here is a screen shot of the command line and status feedback that was created when importing the example tablespace.
  7. You can now modify the tablespace so that it is in a READ WRITE state using the command show here.

The transporting of tablespaces as if they were ordinary files (except for the meta-data, which must be imported) reduces the time it takes to copy or move data from one database to another. In fact, the greater the size of the data files, the more time is saved compared to a "normal" export and import of the data.

Transportable TableSpaces - Exercise

Click the Exercise link below to practice creating a locally managed tablespace and exporting it for transport.
Transportable TableSpaces - Exercise
The next lesson looks into new features and enhancements you will use when handling READ ONLY tablespaces.

SEMrush Software