Moving large volumes of data in an Oracle Database can be challenging, especially when dealing with terabytes of tables and indexes. Traditional methods like Export/Import (exp/imp) or even Data Pump can be slow and time-consuming.
This is where the Transportable Tablespace (TTS) feature shines, allowing you to migrate data faster and more efficiently.
Whether you’re an Oracle DBA or a database enthusiast, this guide will help you perform a smooth and successful transportable tablespace migration.
What Is a Transportable Tablespace (TTS)?
A Transportable Tablespace is an Oracle feature that lets you copy entire tablespaces—including their physical datafiles—from one database to another without exporting and importing all rows.
Instead of moving millions of records one by one, you:
- Copy the physical datafiles at the operating system level.
- Export and import only the metadata using Oracle Data Pump.
This dramatically reduces downtime and is ideal for high-volume Oracle database migrations.
Benefits of Transportable Tablespaces
- ⚡ Speed: No need for a full export/import.
- 💾 Efficiency: Works well for terabyte-scale databases.
- 🔀 Flexibility: Supports same-platform and cross-platform migrations.
- 🛡️ Reliability: Maintains data integrity while reducing downtime.
Key Requirements Before Starting
Before you begin your Oracle TTS migration, make sure these conditions are met:
- Database Compatibility
- Source and target Oracle databases should be on the same version or compatible versions.
- Cross-platform transport is possible if both platforms support the same endianness, or you use RMAN to convert datafiles.
- Tablespace Must Be Read-Only
- The tablespace you transport must be set to READ ONLY before copying the files.
- Required Privileges
- Use a user with EXP_FULL_DATABASE and IMP_FULL_DATABASE roles.
- Character Set Compatibility
- Ideally, both databases should share the same character set. If not, check Oracle’s documentation for conversion requirements.
Step-by-Step Transportable Tablespace Migration
Below is a real-world Oracle DBA workflow to transport a tablespace.
1️⃣ Identify the Tablespace to Transport
Use the query below to list the datafiles you plan to transport:
SELECT tablespace_name, file_name
FROM dba_data_files
WHERE tablespace_name IN ('SALES_TBS');
2️⃣ Set the Tablespace to Read-Only
To ensure data consistency, make the tablespace read-only:
ALTER TABLESPACE SALES_TBS READ ONLY;
3️⃣ Validate the Tablespace
Before proceeding, check for self-containment:
EXEC DBMS_TTS.TRANSPORT_SET_CHECK('SALES_TBS', TRUE);
SELECT * FROM transport_set_violations;
✅ Tip: If this query returns rows, fix the violations before continuing.
4️⃣ Export Metadata Using Data Pump
Export only the metadata (object definitions, schema information):
expdp system/password DUMPFILE=tts_sales.dmp DIRECTORY=DATA_PUMP_DIR \
TRANSPORT_TABLESPACES=SALES_TBS LOGFILE=tts_sales.log
5️⃣ Copy Datafiles to the Target Database
Use SCP, FTP, or any secure file transfer method:
scp /u01/oradata/ORCL/sales_tbs01.dbf oracle@targetserver:/u01/oradata/ORCL/
6️⃣ Import Metadata on the Target Database
On the destination database, run:
impdp system/password DUMPFILE=tts_sales.dmp DIRECTORY=DATA_PUMP_DIR \
TRANSPORT_DATAFILES='/u01/oradata/ORCL/sales_tbs01.dbf' LOGFILE=tts_sales_imp.log
This step registers the tablespace and objects in the target database.
7️⃣ Make the Tablespace Read-Write (Optional)
ALTER TABLESPACE SALES_TBS READ WRITE;
Cross-Platform Transport (Optional)
If you’re migrating between different operating systems (e.g., Linux → Windows), use RMAN to convert the datafiles:
RMAN> CONVERT DATAFILE '/u01/oradata/ORCL/sales_tbs01.dbf'
TO PLATFORM='Linux x86 64-bit'
FORMAT '/u01/oradata/ORCL/sales_tbs01_linux.dbf';
This ensures the datafiles are compatible with the target platform.
Pro Tips for Oracle DBAs
- Always Backup First: Create a full backup of both source and target databases.
- Use Parallelism: For very large metadata exports, use Data Pump parallelism to speed up operations.
- Monitor Logs: Keep an eye on export/import logs for errors or warnings.
- Plan Downtime: Even though TTS is fast, plan a maintenance window for setting tablespaces to read-only.
Final Thoughts
The Transportable Tablespace feature is a must-know tool for every Oracle DBA.
It simplifies large database migrations, reduces downtime, and provides a reliable way to move data between systems—whether you’re consolidating databases, archiving data, or migrating to a new platform.
By following the steps above, you can confidently perform a Transportable Tablespace migration in Oracle Database and keep your business running smoothly.
