Blog do Marcio Portes

Transportable tablespace é a forma mais rápida de mover grande volume de dados de um database para outro. Na versão 10g podemos inclusive mover dados entre plataformas.

Vamos a um exemplo na versão 9i. (9.2.0.4)
Criando uma tablespace com 2 datafiles.

ops$t_mp00@MRP920> create tablespace tts_teste datafile 
  2  'c:\oracle\oradata\tts_t01.dbf' size 2m reuse
  3  autoextend off;

Tablespace created.

ops$t_mp00@MRP920>
ops$t_mp00@MRP920> alter tablespace tts_teste add datafile 
  2  'c:\oracle\oradata\tts_t02.dbf' size 2m reuse
  3  autoextend off;

Tablespace altered.

ops$t_mp00@MRP920>

Criei objeto dentro da tablespace.

ops$t_mp00@MRP920> create table t tablespace tts_teste
  2  as select * from all_objects where rownum <= 200;  Table created.  ops$t_mp00@MRP920>
ops$t_mp00@MRP920> select count(*) from t;

     COUNT(*)
-------------
          200

1 row selected.

É necessário que a tablespace esteja em read only, para que os blocos da buffer cache
sejam baixados para os datafiles.

ops$t_mp00@MRP920>
ops$t_mp00@MRP920> alter tablespace tts_teste read only;

Tablespace altered.

ops$t_mp00@MRP920>

O comando de export.

ops$t_mp00@MRP920> host exp TRANSPORT_TABLESPACE=y tablespaces=tts_teste

Export: Release 9.2.0.4.0 - Production on Fri Jul 2 17:18:12 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Username: / as sysdba  
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
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_TESTE ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                              T
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

ops$t_mp00@MRP920>

Consideração: É necessário o usuário SYS para o export.
Aqui eu to simulando a transferência.

ops$t_mp00@MRP920> drop tablespace tts_teste including contents;

Tablespace dropped.

ops$t_mp00@MRP920>
ops$t_mp00@MRP920> select count(*) from t;
select count(*) from t
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

ops$t_mp00@MRP920>

Fazendo a transferência, no meu caso eu só troquei de nome dos datafiles.

ops$t_mp00@MRP920> host move c:\oracle\oradata\tts_t01.dbf 
c:\oracle\oradata\delete_me01.dbf

ops$t_mp00@MRP920> host move c:\oracle\oradata\tts_t02.dbf 
c:\oracle\oradata\delete_me02.dbf

ops$t_mp00@MRP920>
ops$t_mp00@MRP920> host imp transport_tablespace=y 
datafiles=(c:\oracle\oradata\delete_me01.dbf, c:\oracle\oradata\delete_me02.dbf)

Import: Release 9.2.0.4.0 - Production on Fri Jul 2 17:18:24 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Username: / as sysdba  
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

Export file created by EXPORT:V09.02.00 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 OPS$T_MP00's objects into OPS$T_MP00
. . importing table                            "T"
Import terminated successfully without warnings.

Novamente o usuário SYS para fazer o import.
Voltou normal, mas lembre-se que a tablespace ainda está em read only.
Basta virá-la para read/write.

ops$t_mp00@MRP920>
ops$t_mp00@MRP920> select count(*) from t;

     COUNT(*)
-------------
          200

1 row selected.

ops$t_mp00@MRP920>
ops$t_mp00@MRP920> alter tablespace tts_teste read write;

Tablespace altered.

ops$t_mp00@MRP920> 

Abraços,