Transportable Tablespaces (TTS)

<- DataPump

Overview

You can move data significantly faster than traditional means using Oracle's Transportable Tablespaces. In moving data with this method, the most time consuming part is copying the datafiles. The export\import process just transfers meta information and tends to be very small.

In the scenario presented here the user is SCOTT and the associated tablespaces are SCOTT_DATA and SCOTT_INDEX.

Prerequisites

• Record the privileges for user.
• Record default and temporary tablespaces for user.
• Confirm that the tablespace(s) are transportable.

  EXEC sys.dbms_tts.transport_set_check('SCOTT_DATA,SCOTT_INDEX', true);
  SELECT * FROM sys.transport_set_violations;
  no rows selected
  

Procedure

On Source System

1. Set tablespaces to READ ONLY.

   ALTER TABLESPACE scott_data  READ ONLY;
   ALTER TABLESPACE scott_index READ ONLY;

2. Export tablespaces.

   # TTS_exp.par  
   DIRECTORY=datapump
   DUMPFILE=scott_tts.dmp
   JOB_NAME=TTS_exp
   LOGFILE=TTS_exp.log 
   TRANSPORT_TABLESPACES=scott_data,scott_index

   OS> expdp system PARFILE=TTS_exp.par

3. Copy datafiles to destination system.

4. Reset tablespaces back to READ WRITE.

   ALTER TABLESPACE scott_data  READ WRITE;
   ALTER TABLESPACE scott_index READ WRITE;

5. Export Metadata (Packages, Triggers, Procs...) for schema 
   then copy to destination system.

   # Usage: expdp system PARFILE=xSchemaMeta.par
   DIRECTORY=datapump
   DUMPFILE=scott_meta.dmp
   JOB_NAME=xMeta
   LOGFILE=xMeta.log
   SCHEMAS=SCOTT
   CONTENT=METADATA_ONLY 

On Destination System

1. Create schema user but do not set the default tablespace.

   CREATE USER scott IDENTIFIED BY "tiger";

2. Copy tablespace datafiles from source to destination.
   They must match paths otherwise you need to use the REMAP command upon import.

3. Import tablespace

   # TTS_imp.par
   DIRECTORY=datapump
   DUMPFILE=scott_tts.dmp
   JOB_NAME=TTS_imp
   LOGFILE=TTS_imp.scott.log
   TRANSPORT_DATAFILES='C:\oradata\scott\scott_data.dbf',
      'C:\oradata\scott\scott_index.dbf'

   OS> impdp system PARFILE=TTS_imp.par

4. Set tablespaces to READ WRITE.
   ALTER TABLESPACE scott_data  READ WRITE;
   ALTER TABLESPACE scott_index READ WRITE;

5. GRANT privileges to schema user.

6. Set default tablespaces.

   ALTER USER scott DEFAULT TABLESPACE scott_data;
   ALTER USER scott TEMPORARY TABLESPACE temp;

7. Import metadata for schema.

   # impdp system PARFILE=impSchemaMeta.par
   DIRECTORY=datapump
   DUMPFILE=scott_meta.DMP
   JOB_NAME=impMeta
   LOGFILE=impMeta.log
   SCHEMAS=SCOTT
   CONTENT=METADATA_ONLY

APPENDIX - Import Screen Output

Success looks like this:

Master table "SYSTEM"."TTS_IMP" successfully loaded/unloaded
Starting "SYSTEM"."TTS_IMP":  system/******** PARFILE=TTS_imp.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."TTS_IMP" successfully completed at 12:45:56

APPENDIX - Test Scenario

CREATE BIGFILE TABLESPACE test
DATAFILE 'C:\oradata1\dbf\test.dbf'
SIZE 25m REUSE
AUTOEXTEND ON NEXT 5m MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
LOGGING;

CREATE USER x15 identified by ""********" DEFAULT TABLESPACE test;
GRANT connect, resource, dba TO x15;

CREATE TABLE x15.test1 (
  id    number,
  title varchar(50),
  CONSTRAINT pk_test1 PRIMARY KEY("ID") USING INDEX TABLESPACE test
)
TABLESPACE test;

INSERT INTO X15.test1 VALUES(1001,'AAA');
INSERT INTO X15.test1 VALUES(1002,'BBB');
INSERT INTO X15.test1 VALUES(1003,'CCC');
COMMIT;

SELECT * FROM X15.test1;
--------------------------------------------------------------------------
SOURCE SYSTEM
1. ALTER TABLESPACE test READ ONLY;
2. expdp system PARFILE=TTS_exp.par
3. Copy datafiles to destination.
   For Local Test:
     a. Copy to zTest dir
     b. ALTER TABLESPACE test OFFLINE;
     c. DROP TABLESPACE test  INCLUDING CONTENTS and DATAFILES;
     d. Copy from zTest back to orig dir.
     e. DROP USER x15;
4. ALTER TABLESPACE test READ WRITE;  <=== If not local test
--------------------------------------------------------------------------
DESTINATION SYSTEM
1. CREATE USER x15 IDENTIFIED BY "********";
2. impdp system PARFILE=TTS_imp.par
3. ALTER TABLESPACE test READ WRITE;
4. Reapply privs and default user settings.
   GRANT connect, resource, dba TO x15;
   ALTER USER x15 DEFAULT TABLESPACE test;
   ALTER USER x15 TEMPORARY TABLESPACE temp;
5. QC
   SELECT * FROM X15.test1;

# expdp system PARFILE=TTS_exp.par
# CREATE or REPLACE DIRECTORY datapump AS 'C:\exports';
# GRANT read,write ON DIRECTORY datapump TO x15;
DIRECTORY=datapump
DUMPFILE=test.dmp
JOB_NAME=TTS_exp
LOGFILE=TTS_exp.log 
REUSE_DUMPFILES=YES
TRANSPORT_TABLESPACES=test

#  impdp system/**** PARFILE=TTS_imp.par
DIRECTORY=datapump
DUMPFILE=test.dmp
JOB_NAME=TTS_imp
LOGFILE=TTS_imp.log
#TRANSPORT_DATAFILES='C:\oradata1\dbf\test.dbf'
TRANSPORT_DATAFILES='C:\temp\test.dbf'

<- DataPump