Transportable Tablespaces

Transportable TS on the same Platform

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

Source

------

SQL>select tablespace_name,status from dba_tablespaces;

SQL>alter tablespace USERDATA read only;

SQL> exec dbms_tts.transport_set_check('USERDATA');

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected

$exp file=userdata.dmp transport_tablespace=Y tablespaces=USERDATA

/ as sysdba

$cp /prod/lab/oradata/userdata01.dbf /prod/rk/oradata

$cp /prod/lab/oradata/userdata02.dbf /prod/rk/oradata

SQL> alter tablespace userdata online;

SQL> select username,default_tablespace from dba_users;

USER2 USERDATA

USER1 USERDATA

Target

-------

grant connect,resource to user2 identified by user2;

grant connect,resource to user1 identified by user1;

$imp file=userdata.dmp transport_tablespace=Y tablespaces=USERDATA datafiles=('/prod/rk/oradata/userdata01.dbf','/prod/rk/oradata/userdata02.dbf')

/ as sysdba

SQL>select tablespace_name from dba_tablespaces;

SQL>alter tablespace USERDATA read write;

SQL> alter user user1 default tablespace USERDATA;

SQL> alter user user2 default tablespace USERDATA;

SQL>conn user1/user1

SQL>select * from tab;

Transportal Tablespace across the Platforms;

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

From Hp Tru64 - Linux

source

------

SQL>select * from v$transportable_platform

-- Endian should match --

SQL> select tablespace_name,status from dba_tablespaces;

SQL> alter tablespace USERDATA read only;

SQL> create directory tts as '/prod/lab/tts';

$mkdir -p /prod/lab/tts

select * from dba_directories;

SQL> exec dbms_tts.transport_set_check('USERDATA');

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected

$ expdp directory=tts dumpfile=userdata.dmp transport_tablespaces=USERDATA

/ as sysdba

cd /prod/lab/tts/

ls

cp /prod/lab/oradata/userdata01.dbf /prod/lab/tts

cp /prod/lab/oradata/userdata02.dbf /prod/lab/tts

SQL>alter tablespace userdata read write;

SQL> select username,default_tablespace from dba_users;

Target

------

$cp /prod/lab/tts /prod/rk/tts

SQL>create directory tts as '/prod/rk/tts';

SQL>grant connect,resource to user2 identified by user2;

SQL>grant connect,resource to user1 identified by user1;

$impdp directory=tts dumpfile=userdata.dmp transport_datafiles=('/prod/rk/tts/userdata01.dbf','/prod/rk/tts/userdata02.dbf')

/as sysdba

SQL>select tablespace_name from dba_tablespaces;

SQL>alter tablespace USERDATA read write;

SQL> alter user user1 default tablespace USERDATA;

SQL> alter user user2 default tablespace USERDATA;

SQL>conn user1/user1

SQL>select * from tab;