Monday, December 20, 2010

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;



















1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete