Transport tablespace between platforms with same endian format using EXP/IMP.

Transportable Tablespaces  This can be used by only export/import.

Please try to check the endian format before doing this TTablespace using exp/imp. The endian format should be same between the platforms , otherwise use RMAN convert as shown in the other post.  

RESTRICTIONS
----------------

1. Character set as well as national character set of the source and the target must be the same.
2. Users whose default tablespace is getting exported must exist in the target database before importing.
3. Target database must not have tablespace of the same name.
4. The tablespace must be self contained to ensure that the table is complete in case of partitioning.

We will consider a tablespace by name "N2" for the example.

EXPORT THE TABLESPACE
------------------------
1. Make the tablespace readonly.
sqlplus> connect sys/*******
sqlplus> alter tablespace N2 read only;

2. Export the tablespace specifying the TABLESPACES and TRANSPORT_TABLESPACE clause
D:\app\product\11.2.0\dbhome_1\BIN>exp "'sys/database as sysdba'" file=d:\transport_tblsp\n2.dmp tablespaces=n2 transport_tablespace=y

Export: Release 11.2.0.1.0 - Production on Thu Jun 2 16:49:12 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing 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 N2 ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                            N21
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.


3. Transfer the meta data export dump file and the copy of the datafile(s) to the target  database. (Could be ftp or copy) as below

D:\transport_tblsp>ftp 10.417.313.812
Connected to 10.417.313.812.
220 (vsFTPd 2.0.5)
User (10.417.313.812:(none)): oracle
331 Please specify the password.
Password:
230 Login successful.
ftp> cd /u01/app/backup
250 Directory successfully changed.
ftp> put n2.dbf
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 10493952 bytes sent in 5.59Seconds 1875.93Kbytes/sec.
ftp> ls
200 PORT command successful. Consider using PASV.
150 Here comes the directory listing.
N2.DBF
226 Directory send OK.
ftp: 8 bytes received in 0.00Seconds 8000.00Kbytes/sec.
ftp> put *.dmp
200 PORT command successful. Consider using PASV.
150 Ok to send data.
226 File receive OK.
ftp: 4096 bytes sent in 0.00Seconds 4096000.00Kbytes/sec.
ftp> ls
200 PORT command successful. Consider using PASV.
150 Here comes the directory listing.
N2.DBF
n2.dmp
226 Directory send OK.
ftp: 16 bytes received in 0.00Seconds 16000.00Kbytes/sec.
ftp>


4. Import the metadata using TABLESPACES, TRANSPORT_TABLESPACE, DATAFILES clause, If more than one datafiles are available then specify all of them comma seperated.
[oracle@hostname backup]$ imp '"sys/******* as  sysdba"' file=n2.dmp TABLESPACES=N2 TRANSPORT_TABLESPACE=y datafiles=/u01/app/oracle/oradata/orcl/N2.DBF

Import: Release 11.2.0.1.0 - Production on Thu Jun 2 17:33:11 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
export client uses WE8MSWIN1252 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing N2's objects into N2
. . importing table                          "N21"
. importing SYS's objects into SYS
Import terminated successfully without warnings.


5. Make the tablespace read write, if required, at the target database
SQL> alter tablespace n2 read write;

Tablespace altered.


These below tables contains a new field PLUGGED_IN which is helpful in identifying the plugged in tabelspaces.
The value of PLUGGED_IN would be YES in dba_tablespaces and 1 in v$datafile if the tablespace/datafile was plugged in.
SQL> select tablespace_name, status, plugged_in from dba_tablespaces;

TABLESPACE_NAME                STATUS    PLU
------------------------------ --------- ---
SYSTEM                         ONLINE    NO
SYSAUX                         ONLINE    NO
UNDOTBS1                       ONLINE    NO
TEMP                           ONLINE    NO
USERS                          ONLINE    NO
EXAMPLE                        ONLINE    YES
APEX                           ONLINE    NO
TEST11                         ONLINE    NO
N2                             ONLINE    YES

SQL> select name, status, plugged_in from v$datafile;

NAME                                               STATUS  PLUGGED_IN
-------------------------------------------------- ------- ----------
/u01/app/oracle/oradata/orcl/system01.dbf          SYSTEM           0
/u01/app/oracle/oradata/orcl/sysaux01.dbf          ONLINE           0
/u01/app/oracle/oradata/orcl/undotbs01.dbf         ONLINE           0
/u01/app/oracle/oradata/orcl/users01.dbf           ONLINE           0
/u01/app/oracle/oradata/orcl/example01.dbf         ONLINE           0
/u01/app/oracle/oradata/orcl/apex01.dbf            ONLINE           0
/u02/SCRIPTS/test11_data_01.dbf                    ONLINE           0
/u01/app/oracle/oradata/orcl/N2.DBF                ONLINE           1

8 rows selected.

Regards,

Navaneeth