TRANSPORTABLE TABLESPACES in oracle 11G

Source machine;

We will transport tablespaces using EXP and EXPDP commands. So we need our database up and running

SQL> startup;

ORACLE instance started.

Database opened.

SQL> create tablespace tts datafile '/u02/oradata/rmanprod/tts.dbf' size 50M;

Tablespace created.

SQL> create user tts identified by tts default tablespace

SQL> create user ttsuser identified by ttsuser default tablespace tts;

User created.

SQL> grant connect,resource to ttsuser;

Grant succeeded.

SQL> conn ttsuser/ttsuser

Connected.

SQL> create table a(a number);

Table created.

SQL> insert into a values(1);

1 row created.

Commit;

SQL> exec dbms_tts.TRANSPORT_SET_CHECK('TTS');

BEGIN dbms_tts.TRANSPORT_SET_CHECK('TTS'); END;

*

ERROR at line 1:

ORA-25153: Temporary Tablespace is Empty

ORA-06512: at line 1

Note :- If you get ORA-25153 error then you need to assign a default temporary tablespace to database.

SQL> alter database default temporary tablespace tempts;

SQL> exec dbms_tts.TRANSPORT_SET_CHECK('TTS');

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected

I f you get any output other than  ‘no rows selected’ then we cannot export tablespace , we need to go through that output and we should take appropriate actions.

SQL> alter tablespace tts read only;

Tablespace altered.

SQL>exit.

[oracle@vmrshoret4db5 ~]$ exp file=tts_exp.dmp log=tts_exp.log TRANSPORT_TABLESPACE=Y TABLESPACES='TTS'

Export terminated successfully without warnings.

Now copy dumpfile and datafile to destination using scp command.

[oracle@vmrshoret4db5 ~]$ scp tts_exp.dmp oracle@192.168.3.18:/$HOME

[oracle@vmrshoret4db5 rmanprod]$ scp tts.dbf oracle@192.168.3.18:/u03/oradata/mls/

Now keep tablespace in read write mode

SQL> alter tablespace tts read write;

Tablespace altered. 

In destination

SQL> startup;

ORACLE instance started.

SQL> create user ttsuser identified by ttsuser;

User created.

[oracle@vmrshoret4db3 ~]$ imp file=tts_exp.dmp log=tts.log datafiles='/u03/oradata/mls/tts.dbf' TRANSPORT_TABLESPACE=Y TABLESPACES=TTS

Import terminated successfully without warnings.

SQL> select file_name from dba_data_files;

FILE_NAME

-----------------------------------------------------------

/u03/oradata/mls/system01.dbf

/u03/oradata/mls/sysaux01.dbf

/u03/oradata/mls/undotbs01.dbf

/u03/oradata/mls/tempts1.dbf

/u03/oradata/mls/tts.dbf

SQL> select plugged_in,tablespace_name from dba_tablespaces;

PLU         TABLESPACE_NAME

---            ------------------------------

NO          SYSTEM

NO          SYSAUX

NO          UNDOTBS1

NO          TEMPTS1

YES          TTS

Note:-If plugged_in value is yes then that tablespace is transported tablespace.

SQL> conn ttsuser/ttsuser;

Connected.

SQL> select * from a;


 Transportable tablespace for different datablock size(db block size) click on below link
http://learnappsdbaskills.blogspot.in/2013/07/transportable-tablespace-for-different.html