Transporting Tablespace.

A transportable tablespace is a mechanism for taking the formatted datafiles of one database, and attaching them to another. Instead of unloading the data from one database to a flat file, or a DMP file, and then inserting that data into the other database, transporting a tablespace lets you move the data as fast as you can copy files.

scott@10G> create tablespace TTS_TBL datafile 'C:\trash\tts_tb1.dbf' size 1m extent management local uniform size 64k;

Tablespace created.

scott@10G> create tablespace TTS_INDX datafile 'C:\trash\tts_indx.dbf' size 1m extent management local uniform size 64k;

Tablespace created.

Elapsed: 00:00:00.64
scott@10G> create user tts_test identified by tts_test default tablespace tts_tbl temporary tablespace temp;

User created.

scott@10G> grant dba to tts_test;

Grant succeeded.

scott@10G> connect tts_test/tts_test;
Connected.
tts_test@10G> create table emp tablespace tts_tbl as select * from scott.emp;

Table created.

tts_test@10G> create index emp_ix on emp(empno) tablespace tts_indx;

Index created.


tts_test@10G> SELECT object_type, object_name, tablespace_name
  2  FROM user_objects, user_segments
  3  WHERE  object_name = segment_name
  4  /

OBJECT_TYPE         OBJECT_NAME                    TABLESPACE_NAME
------------------- ------------------------------ ------------------------------
TABLE               EMP                            TTS_TBL
INDEX               EMP_IX                         TTS_INDX

Prior to attempting to export, we need to ensure we have a self‐contained set of objects for transport. We can transport a table without its indexes, but we cannot transport an index without its tables. The following shows the routine that we should use to check if a tablespace, or set of tablespaces, are self‐contained.



tts_test@10G> EXEC sys.dbms_tts.transport_set_check('TTS_TBL',TRUE);

PL/SQL procedure successfully completed.

tts_test@10G> select * from sys.transport_set_violations;

no rows selected

tts_test@10G> EXEC sys.dbms_tts.transport_set_check('TTS_INDX',TRUE);

PL/SQL procedure successfully completed.

tts_test@10G> select * from sys.transport_set_violations;

VIOLATIONS
------------------------------------------------------------------------------------------------------------
Index TTS_TEST.EMP_IX in tablespace TTS_INDX points to table TTS_TEST.EMP in tablespace TTS_TBL

tts_test@10G> EXEC sys.dbms_tts.transport_set_check('TTS_TBL,TTS_TBL',TRUE);

PL/SQL procedure successfully completed.


tts_test@10G> select * from sys.transport_set_violations;

no rows selected


This shows that we can transport TTS_TBL, because it only contains table data and is selfcontained. Any attempt to transport TTS_INDX, however, would fail since it contains indexes, but not the tables on which they are based. Lastly, both the tablespaces, TTS_TBL and TTS_INDX may be transported together, as we would be transporting both the tables and the indexes.

tts_test@10G> CONNECT scott/oracle
Connected.
scott@10G> alter tablespace tts_tbl read only;

Tablespace altered.

scott@10G> alter tablespace tts_indx read only;

Tablespace altered.

Exporting Tablespace
=====================
D:\QA>exp userid="""sys/oracle as sysdba""" transport_tablespace=Y tablespaces=(TTS_TBL,TTS_INDX) file=d:\qa\exp_tbs.dmp

Export: Release 10.2.0.3.0 - Production on Wed Feb 24 23:00:35 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TTS_TBL ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                            EMP
For tablespace TTS_INDX ...
. exporting cluster definitions
. exporting table definitions
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

Importing Tablespace
=====================
D:\QA>imp userid="""sys/itg9999@dbss8145:1521:testdb as sysdba""" file=d:\qa\exp_tbs.dmp transport_tablespace=Y "datafiles=(d:\qa\tts_TB1.DBF,d:\qa\tts_INDX.DBF)"

Import: Release 10.2.0.3.0 - Production on Wed Feb 24 23:13:00 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing TTS_TEST's objects into TTS_TEST
. . importing table                          "EMP"
. importing SYS's objects into SYS
Import terminated successfully without warnings.

D:\QA>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Feb 24 23:15:16 2010

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

idle> connect tts_test/tts_test@dbss8145;
Connected.
tts_test@dbss8145> update emp set ename = lower(ename);
update emp set ename = lower(ename)
       *
ERROR at line 1:
ORA-00372: file 6 cannot be modified at this time
ORA-01110: data file 6: 'D:\QA\TTS_TB1.DBF'


Elapsed: 00:00:00.07
tts_test@dbss8145> connect scott/oracle@dbss8145;
Connected.
scott@dbss8145> alter tablespace tts_tbl read write;

Tablespace altered.

Elapsed: 00:00:00.20
scott@dbss8145> alter tablespace tts_indx read write;

Tablespace altered.

Elapsed: 00:00:00.17
scott@dbss8145> connect tts_test/tts_test;
Connected.
tts_test@dbss8145> update emp set ename = lower(ename);

14 rows updated.

Elapsed: 00:00:00.04
tts_test@dbss8145>