[oracle@localhost ~]$ export ORACLE_SID=path
[oracle@localhost ~]$ sqlplus '/ as sysdba'

SQL> select name from v$database;
NAME
---------
PATH

SQL> select status from v$instance;
STATUS
------------
OPEN

12:25:33 SQL> select username,default_tablespace from dba_users;

12:26:02 SQL> create tablespace sid datafile '/u01/app/oracle/oradata/path/sid1.dbf' size 10m;
Tablespace created.

12:27:57 SQL> create user tts_user identified by tts default tablespace sid;
User created.

12:28:32 SQL> select username,default_tablespace from dba_users;

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW SYSTEM
.
.
TTS_USER SID
.
.
EXFSYS SYSAUX

12:29:50 SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ------------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
.
.
SID ONLINE

9 rows selected.

12:30:17 SQL> alter tablespace sid read only;
Tablespace altered.

12:30:33 SQL> exec dbms_tts.transport_set_check('SID');
PL/SQL procedure successfully completed.

12:31:03 SQL> select * from transport_set_violations;
no rows selected

12:31:20 SQL> host pwd
/home/oracle
12:31:29 SQL> host exp file=sid.dmp transport_tablespace=Y tablespaces=sid

Export: Release 10.2.0.1.0 - Production on Fri Sep 24 12:32:01 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Username: / 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 US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace SID ...
. exporting cluster definitions
. exporting table definitions
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

Copy the datafile from one database to the another database
In my example both the database were on the same machine.

oracle@localhost ~]$ cp /u01/app/oracle/oradata/path/sid1.dbf /u01/app/oracle/oradata/orcl1/sid1.dbf


12:32:18 SQL> alter tablespace sid online;
Tablespace altered.

[oracle@localhost ~]$ export ORACLE_SID=orcl1
[oracle@localhost ~]$ sqlplus '/ as sydba'

SQL> select name from v$database;
NAME
---------
ORCL1

SQL> select status from v$instance;
STATUS
------------
OPEN

SQL> create user tts_user identified by tts_user;
User created.

SQL> grant connect,resource to tts_user;
Grant succeeded.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE

6 rows selected.


copy the datafile from the main server to the server where datafile had to be transported.


SQL> host imp file=sid.dmp transport_tablespace=Y tablespaces=sid datafiles='/u01/app/oracle/oradata/orcl1/sid1.dbf'

Import: Release 10.2.0.1.0 - Production on Fri Sep 24 12:42:03 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Username: / 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 file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
Import terminated successfully without warnings.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ -------------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
SID READ ONLY

7 rows selected.

SQL> alter tablespace sid read write;
Tablespace altered.


SQL> conn tts_user/tts_user
Connected.