Transportable tablespace

Verify whether tablespace can be transported or not

EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'BAS_USER_LOG3', incl_constraints => TRUE);

Run query to verify that there is no violation

SELECT * FROM transport_set_violations;

ALTER TABLESPACE USER_LOG3 READ ONLY;

Create directory for dump files

CREATE OR REPLACE DIRECTORY MY_DIR AS 'D:\DUMP';

SELECT * FROM DBA_DIRECTORIES

GRANT READ, WRITE ON DIRECTORY MY_DIR TO SYSTEM;

expdp userid='system/******@devdb ' directory=MY_DIR transport_tablespaces=USER_LOG dumpfile=USER_LOG.dmp logfile=USER_LOG.log

Return tablespace to read write mode after transporting metadata

ALTER TABLESPACE USER_LOG READ WRITE;

Destination Database

Create any users in the destination database that owned objects within the tablespace being transported, assuming they do not already exist.

CONN / AS SYSDBA

CREATE USER test_user IDENTIFIED BY test_user;
GRANT CREATE SESSION, CREATE TABLE TO test_user;

CONN / AS SYSDBA

CREATE OR REPLACE DIRECTORY MY_DIR AS 'D:\DUMP';
GRANT READ, WRITE ON DIRECTORY MY_DIR TO system;

Plug in the tablespace

impdp userid=system/****** directory=MY_DIR dumpfile=USER_LOG.dmp logfile=USER_LOG_IMP.log transport_datafiles='/u01/app/oracle/oradata/DB11GB/test_data01.dbf'

Make it read write

ALTER TABLESPACE test_data READ WRITE;

SELECT tablespace_name, plugged_in, status
FROM   dba_tablespaces
WHERE  tablespace_name = 'TEST_DATA';