Trasnportable Tablespace

Contents:- Transportable Tablespace

Concepts:-

Transportable tablespace is used to move the tablespace from one database to another database. Before move tablespace from one database to another database we have to check violations of the tablespace as well as need to make tablespace read only.

Source Database   :-        evosys

Target Database   :-        Safal

Source Side:- Evosys

$>. Oraenv

evosys

Sql> create tablespace demot datafile ‘/u01/app/oracle/oradata/evosys/demo01.dbf’ size 10m autoextend on next 5m maxsize 20m extent management local segment space management auto;

Sql> create user demo_user identified by demo_user default tablespace demot;

Sql> grant create session,create table to demo_user;

sql> conn demo_user/demo_user

sql> create table demo_table(id number,description varchar2(100));

sql> insert into demo_table(select level,’description for ‘||level from dual connect by level<=10000);

sql> commit;

sql> conn sys as sysdba

sql> select tablespace_name,plugged_in,status from dba_tablespaces;

$>mkdir –p /u01/app/oracle/tts

Sql> create or replace directory tts_dir as ‘/u01/app/oracle/tts’;

Check is there any violations in a tablespace.

Sql> exec dbms_tts.transport_set_check(ts_list=>’demot’ incl_constratins=>TRUE);

Sql> select * from transport_check_violations;

Sql> alter tablespace demot read only;

$>expdp userid=sys/evosys directory=tts_dir transport_tablespaces=demo dumpfile=demo.dmp logfile=demo.log

Sys as sysdba(username)

Evosys(password)

Target Side:- Safal

Move demo.dmp and datafile(demo01.dbf) to destination location.

$>. Oraenv

Safal

$>sqlplus sys as sysdba

Sql> create user demo_user identified by demo_user;

Sql> grant create table,create session to demo_user;

Sql> create or replace directory tts_dir as ‘/u01/app/oracle/tts’;

$>impdp userid=sys/safal directory=tts_dir transport_datafiles=’ /u01/app/oracle/oradata/safal/demo01.dbf’ dumpfile=demo.dmp logfile=demo.log

Sys as sysdba(username)

Safal(password)

Now check your tablespace is successfully copied to destination database and data also came.

Sql> select tablespace_name,plugged_in,status from dba_tablespaces;

Sql> alter tablespace demo read write;

Sql> conn demo_user/demo_user

Sql> select * from demo_Table;

-------------Successfully Completed--------------------