Step-by-Step Guide: Migrating a Tablespace Between Oracle Databases


Want to quickly copy thousands of tables from one Oracle database to another in just 5 minutes — even with millions of rows per table, and even across different platforms? It's possible, and here's how.

Transport data quickly

In Oracle, all tables reside in tablespaces, and each tablespace has one or more datafiles attached to it. From Oracle 9i onwards, Oracle introduced transportable tablespaces — you can transport a tablespace along with its datafiles from one Oracle database to another.

Migrating a tablespace involves creating a transportable tablespace set and integrating it into the target database. Here's a detailed guide.

Prerequisites

  1. Compatibility: ensure both source and target databases are compatible in character sets and platform endian formats — verify with SELECT * FROM V$TRANSPORTABLE_PLATFORM;
  2. User Permissions: the user performing the migration needs DBA or equivalent privileges.
  3. Tablespace Readiness: the tablespace must be self-contained (no references to objects outside it).
  4. Schema Names: ensure both databases have the same schema names (e.g. if source has SCOTT, target must too).

Step 1: Set Tablespace to Read-Only Mode

On the source database, make the tablespace read-only to ensure data consistency during export.

ALTER TABLESPACE tablespace_name READ ONLY;

Step 2: Export the Tablespace Metadata

Use the Data Pump Export (expdp) utility to export metadata for the tablespace.

expdp system/password DIRECTORY=dp_dir DUMPFILE=tablespace.dmp LOGFILE=tablespace.log TRANSPORT_TABLESPACES=tablespace_name

Step 3: Copy the Tablespace Data Files

Manually copy the physical data files from the source server to the target server. Locate them with:

SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'tablespace_name';

Then transfer with a secure copy tool:

scp /source_path/file_name.dbf user@target_host:/target_path

Step 4: Import the Tablespace Metadata

On the target database, use Data Pump Import (impdp):

impdp system/password DIRECTORY=dp_dir DUMPFILE=tablespace.dmp LOGFILE=tablespace_import.log TRANSPORT_DATAFILES='/target_path/file_name.dbf'

TRANSPORT_DATAFILES: the location of the copied data files.

Step 5: Make the Tablespace Read-Write

ALTER TABLESPACE tablespace_name READ WRITE;

Step 6: Verify the Migration

Validate objects with:

SELECT * FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'tablespace_name';

Then confirm that applications or users can access the data without issues.

Step 7: Clean Up (Optional)

If the tablespace is no longer required on the source database, drop it:

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;
Tips for a Successful Migration

Practical Example: Migrating the USERS_DATA Tablespace

Scenario: Source database ORCL_SRC, target database ORCL_TGT, tablespace USERS_DATA, data file at /u01/app/oracle/oradata/ORCL_SRC/users_data01.dbf, Oracle directory DATA_PUMP_DIR.

  1. Set the tablespace read-only: ALTER TABLESPACE USERS_DATA READ ONLY;
  2. Export metadata: expdp system/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=users_data.dmp LOGFILE=users_data.log TRANSPORT_TABLESPACES=USERS_DATA
  3. Copy the data file with scp to the target server.
  4. Import metadata on the target: impdp system/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=users_data.dmp LOGFILE=users_data_import.log TRANSPORT_DATAFILES='/u01/app/oracle/oradata/ORCL_TGT/users_data01.dbf'
  5. Set the tablespace read-write: ALTER TABLESPACE USERS_DATA READ WRITE;
  6. Verify: SELECT * FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'USERS_DATA';
  7. Optionally drop the source tablespace once confirmed.

The USERS_DATA tablespace is successfully migrated from ORCL_SRC to ORCL_TGT, ensuring seamless data availability on the target database.

This method provides a robust and efficient way to migrate tablespaces between Oracle databases while minimizing downtime.