Wednesday, August 20, 2008

Transportable tablespace

# on the source database

ALTER TABLESPACE test READ ONLY;
select file_name from dba_data_files where tablespace_name = 'TEST';

exp
userid=\"sys/change_on_install@olddb as sysdba\"
transport_tablespace=y
tablespaces=test
triggers=y
constraints=y
grants=y
file=test.dmp

copy test_01.dbf to the filesystem for the destination database

ALTER TABLESPACE fact1 READ WRITE;

# on the destination database

imp
userid=\"sys/change_on_install@newdb as sysdba\"
transport_tablespace=y
datafiles='test_01.dbf'
file=test.dmp

ALTER TABLESPACE test READ WRITE;
select file_name from dba_data_files where tablespace_name = 'TEST';