Transport tablespace over db links

You do not want to export the metadata from the source database, but rather use a database link to get this.

As prerequisite, you have made a set of self-contained tablespaces in read-only mode and you have copied the datafiles.

SQL> create tablespace test datafile '/u02/oradata/db01/test01.dbf' size 10m;

Tablespace created.

SQL> create table scott.x(x number) tablespace test;

Table created.

SQL> insert into scott.x values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> alter tablespace test read only;

Tablespace altered.

$ scp srv01:/u02/oradata/db01/test01.dbf /u02/oradata/db02

create a database link on the target database DB02

SQL> create database link l using 'DB01';

Database link created.

Then import the tablespace via db link

impdp scott/tiger network_link=l transport_tablespaces=TEST transport_datafiles=/u02/oradata/db02/test01.dbf logfile=DATA_PUMP_LOG:impdp.log

This avoid the “expdp” step, but it does not avoid copying the datafiles

Categorized as dba Tagged

By Laurent Schneider

Oracle Certified Master


  1. I think you could use dbms_file_transfer to transfer the files from within plsql (but I haven’t tested it).

  2. As much as I love Data Pump network/db link based exp/imp jobs, I’d probably go for [RMAN] “active database cloning” in similar scenario..

  3. Pingback: Laurent Schneider ยป Transport tablespace over db links « Nowoczesne przeprowadzki

Leave a comment

Your email address will not be published.