A Testcase for Transportable Tablespace

To be familiar myself with the transportable tablespace feature, I conducted this test:

Test Steps

1. Create a tablespace called TEST_TTS for test

connect / as sysdba
create tablespace test_tts datafile='e:\temp\test_tts01.dbf' size 2M;
connect scott/tiger
create table t tablespace test_tts as
select rownum id, a.* from all_objects a where rownum <100;


2. Check if the tablespace is self-contained (optional)

sys@TEST10GR> execute dbms_tts.transport_set_check('test_tts',true);

PL/SQL procedure successfully completed.

3. Make the tablespace READ ONLY:

SQL>  alter tablespace test_tts read only;

scott@TEST10GR> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
~
TEST_TTS                       READ ONLY

4. Export the tablespace. At the source host, from the OS prompt, issue:

#> exp tablespaces=test_tts transport_tablespace=y file=exp_test_tts.dmp

Export: Release 10.2.0.1.0 - Production on Mon Dec 17 13:38:45 2007

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


Username: sys/oracle as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.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 TEST_TTS ...
. 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

Notes: Must log in as SYSDBA to perform the exp

5. Copy the files exp_test_tts.dmp and e:\temp\test_tts01.dbf to the directory e:\Denis_File\temp at the target host

6. Make the tablespace read write at source database

SQL> alter tablespace test_tts read write;

7. At the target host, plug the tablespace into the target database.
From the OS command prompt, issue:

cd E:\Denis_Files\temp
E:\Denis_Files\temp>ls -lh  *.DBF *.dmp
-rw-r--r-- 1 Denis Sun None 2.1M Dec 17 09:28 TEST_TTS01.DBF
-rw-r--r-- 1 Denis Sun None 4.0K Dec 17 09:40 exp_test_tts.dmp

E:\Denis_Files\temp>imp tablespaces=test_tts transport_tablespace=y file=exp_tes
t_tts.dmp datafiles='E:\Denis_Files\temp\TEST_TTS01.DBF' tts_owners=scott

Import: Release 10.2.0.1.0 - Production on Mon Dec 17 13:45:42 2007

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

Username: sys/oracle as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion 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 SCOTT's objects into SCOTT
. . importing table                            "T"
. importing SYS's objects into SYS
Import terminated successfully without warnings.

Notes: we can specify fromuser, touser to map differect schemas.

8. Verify the target database

SQL> select name from v$datafile;

NAME
-------------------------------------------------------------

D:\ORADATA\DB10G\SYSTEM01.DBF
D:\ORADATA\DB10G\UNDOTBS01.DBF
D:\ORADATA\DB10G\SYSAUX01.DBF
D:\ORADATA\DB10G\USERS01.DBF
E:\DENIS_FILES\TEMP\TEST_TTS01.DBF


9. Log into the source and target database, issue

SQL> drop tablespace test_tts including contents and datafiles;




Notes:
-----

Find the byte order of operating systems

select * from v$transportable_platform order by platform_id

PLATFORM_ID PLATFORM_NAME                       ENDIAN_FORMAT
----------- ----------------------------------- --------------
1           Solaris[tm] OE (32-bit)             Big
2           Solaris[tm] OE (64-bit)             Big
3           HP-UX (64-bit)                      Big
4           HP-UX IA (64-bit)                   Big
5           HP Tru64 UNIX                       Little
6           AIX-Based Systems (64-bit)          Big
7           Microsoft Windows IA (32-bit)       Little
8           Microsoft Windows IA (64-bit)       Little
9           IBM zSeries Based Linux             Big
10          Linux IA (32-bit)                   Little
11          Linux IA (64-bit)                   Little
12          Microsoft Windows 64-bit for AMD    Little
13          Linux 64-bit for AMD                Little
15          HP Open VMS                         Little
16          Apple Mac OS                        Big
17          Solaris Operating System (x86)      Little
18          IBM Power Based Linux               Big