Transportable Tablespaces Features in Oracle

 Transportable Tablespaces Features in Oracle

The transportable tablespaces feature to copy/move subset of data (set of user tablespaces) from an Oracle database and plug it in to another Oracle database. 

The tablespaces being transported can be either dictionary managed or locally managed.

Example:

SQL> CREATE TABLESPACE fusion

DATAFILE '+DATA01'

SIZE 1M AUTOEXTEND ON NEXT 1M;  2    3

Tablespace created.

CREATE USER fusion IDENTIFIED BY ****

DEFAULT TABLESPACE fusion

TEMPORARY TABLESPACE temp

QUOTA UNLIMITED ON fusion;

GRANT CREATE SESSION, CREATE TABLE TO fusion;

SQL> show user;

USER is "FUSION"

SQL> CREATE TABLE test_tab (

  id          NUMBER,

  description VARCHAR2(50),

  CONSTRAINT test_tab_pk PRIMARY KEY (id)

);

  2    3    4    5

Table created.

insert some values in the table.

insert 1000 rows

commit;

issue 

select * from v$transportable_platform order by platform_id;

select tp.PLATFORM_NAME, tp.ENDIAN_FORMAT from v$database d, v$transportable_platform tp where d.platform_name=tp.platform_name;

PLATFORM_NAME

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

ENDIAN_FORMAT

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

Linux x86 64-bit

Little

TTS requires all the tablespaces, which we are moving, must be self contained. This means that the segments within the migration tablespace set cannot have dependency to a segment in a tablespace out of the transportable tablespace set. This can be checked using the DBMS_TTS.TRANSPORT_SET_CHECK procedure.

SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('FUSION', TRUE);

PL/SQL procedure successfully completed.

OR

EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'FUSION', incl_constraints => TRUE);

TO check whether any voilations

SQL> SELECT * FROM transport_set_violations;

no rows selected

ALTER TABLESPACE fusion READ ONLY;

SQL> CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/';

GRANT READ, WRITE ON DIRECTORY temp_dir TO system;

Directory created.

SQL>

Grant succeeded.

take the export

expdp userid='system/****@connect string' transport_tablespace=y tablespaces=FUSION file=FUSION_EXP.dmp log=FUSION_exp.log

once the export completes alter the tablespace from readonly to read write.

sql>alter tablespace fusion read write;

Target server:

+++++++++++

SQL> CREATE TABLESPACE fusion

DATAFILE '/u02/app/oracle/oradata/soorya/fusion01.dbf'

SIZE 1M AUTOEXTEND ON NEXT 1M;

  2    3

Tablespace created.

SQL>

SQL>

SQL> CREATE USER fusion IDENTIFIED BY ***

DEFAULT TABLESPACE fusion

TEMPORARY TABLESPACE temp

QUOTA UNLIMITED ON fusion;  2    3    4

User created.

SQL> GRANT CREATE SESSION, CREATE TABLE TO fusion;

Grant succeeded.

CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/';

GRANT READ, WRITE ON DIRECTORY temp_dir TO system;

impdp userid='system/***@*****' directory=temp_dir transport_tablespace=y datafiles='/u02/app/oracle/oradata/soorya/fusion01.dbf' tablespaces=fusion file=FUSION_EXP.dmp log=FUSION_EXP.log

source 

alter tablespace fusion read write;

check the table which was in target and source.