Lesson 4	Transportable tablespaces
Objective	Define transportable tablespaces for Oracle 23ai.

Transportable Tablespaces in Oracle 23ai

A transportable tablespace (TTS) is an Oracle feature for moving a large set of database objects by physically moving the tablespace datafiles and separately moving the metadata that describes those objects. Instead of exporting and importing every row, you transport the tablespace files “as files,” which is typically much faster for large datasets.

What You Actually Transport

Transporting a tablespace involves two parts:

Datafiles for the tablespace(s) you are transporting (these contain the table/index segment blocks).
Metadata describing the objects inside those tablespaces (tables, indexes, constraints, grants, etc.). In modern Oracle workflows, metadata is commonly moved with Data Pump.

The important design constraint is that the transport set must be self-contained: objects inside the transported tablespaces must not depend on objects that remain outside the set.

Why Transportable Tablespaces Matter

Transportable tablespaces are used when you want to move or share large, relatively stable collections of data efficiently, such as:

Migrations and consolidations where moving many gigabytes (or terabytes) row-by-row is too slow.
Data warehousing workflows where historical data is loaded and then transported into a warehouse environment.
Distributing read-mostly reference data across environments.
Archiving by moving older data into a separate database while keeping application data smaller.

High-Level Workflow

Oracle 23ai transportable tablespaces flow: validate transport set, set tablespaces read only, export metadata with Data Pump, copy/convert datafiles, import metadata, set tablespaces read write and validate. — Oracle 23ai transportable tablespaces workflow (modernized): validate the transport set, place tablespaces in READ ONLY, export metadata (Data Pump), copy (and if needed convert) the datafiles, import metadata, then set READ WRITE and validate.

Key Steps and the SQL/Tools Involved

1) Validate that the transport set is self-contained

Before you move anything, verify that objects inside the tablespaces do not depend on objects outside the set. A common approach is to run Oracle’s transport set check and review violations.


-- Example (conceptual): check that the transport set is self-contained
-- (The specific procedure name and parameters depend on your Oracle release and environment.)
-- After the check, query the violations view/table to see what must be included or refactored.

2) Put the tablespaces in READ ONLY (consistency window)

Oracle requires a consistent point-in-time view of the transported datafiles. The usual method is to set the tablespace(s) to READ ONLY:


ALTER TABLESPACE warehouse1 READ ONLY;

If you are transporting a set of tablespaces, repeat this for each one in the transport set.

3) Export metadata (Data Pump)

In modern Oracle environments, metadata movement is commonly handled using Data Pump export. The export captures the object definitions and related metadata for the transport set.


-- Example shape of a Data Pump transport export (adjust user, directory, filenames, and tablespaces)
expdp user/password DIRECTORY=dp_dir DUMPFILE=tts_meta.dmp LOGFILE=tts_meta.log TRANSPORT_TABLESPACES=warehouse1

4) Copy the datafiles (and convert if cross-platform)

Copy the tablespace datafiles to storage that is accessible by the target database host. If you are moving between platforms with different endian formats, datafile conversion may be required (commonly via RMAN conversion workflows).


-- Example (conceptual): cross-platform conversion is typically handled with RMAN conversion workflows
-- The exact command varies by platform pair and storage layout.

5) Import metadata at the target (Data Pump)

On the target database, import the metadata and point Data Pump to the transported datafiles.


-- Example shape of a Data Pump transport import (adjust directory, dumpfile, and datafile paths)
impdp user/password DIRECTORY=dp_dir DUMPFILE=tts_meta.dmp LOGFILE=tts_imp.log TRANSPORT_DATAFILES='/u02/oradata/warehouse1_01.dbf'

6) Set READ WRITE and validate

After import and verification steps, place the tablespace back in READ WRITE mode:


ALTER TABLESPACE warehouse1 READ WRITE;

Then perform validation appropriate to your environment (row counts, key constraints, application smoke tests, and query performance checks).

Practical Notes and Constraints

Self-contained requirement is the main design gate. If the transported objects reference objects outside the set (for example, constraints or dependencies across tablespaces), you must either include the referenced tablespaces or refactor the dependencies.
READ ONLY is typically required for the source transport set. Plan the READ ONLY window to minimize operational impact.
Cross-platform moves may require conversion. Account for platform compatibility, conversion time, and storage staging.
Not every object type is always a perfect fit for TTS. In real migrations you should confirm supportability for your specific object mix (for example, specialized object types or features that may have transport nuances).

Transportable Tablespaces Exercise

The next step is to practice the concept. Click the Exercise link below to work through a transportable tablespaces scenario.
Transportable TableSpaces - Exercise
In the next lesson, you will examine operational considerations related to READ ONLY tablespaces.