Transportable Tablespace

Transportable Tablespaces

1)Create tablespace

CREATE TABLESPACE test_data

  DATAFILE '/home/cybage/app/cybage/oradata/orcl/test_data01.dbf'

  SIZE 1M AUTOEXTEND ON NEXT 1M;

2)Create User assign created tablespace name

CREATE USER test_user IDENTIFIED BY test_user

  DEFAULT TABLESPACE test_data

  TEMPORARY TABLESPACE temp

  QUOTA UNLIMITED ON test_data;

3)Provide Grants

grant connect,resource,CREATE SESSION, CREATE TABLE to test_user;

4)Create table from that user and add the data

CREATE TABLE test_tab (

  id          NUMBER,

  description VARCHAR2(50),

  CONSTRAINT test_tab_pk PRIMARY KEY (id)

);

INSERT /*+ APPEND */ INTO test_tab (id, description)

SELECT level,

       'Description for ' || level

FROM   dual

CONNECT BY level <= 10000;

5)to check whether database self contained

CONN / AS SYSDBA

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

6) ALTER TABLESPACE test_data READ ONLY;

7)Take export

 expdp directory=data_pump_dir transport_tablespaces=test_data dumpfile=test_data.dmp logfile=test_data_exp.log

.

8)Export of tablespaces

expdp directory=data_pump_dir transport_tablespaces=test_data dumpfile=test_data.dmp logfile=test_data_exp.log

9)Transport dump file and data file that to destination directory

Destination Database

10)create user and provide grants

CREATE USER test_user IDENTIFIED BY test_user;

GRANT CREATE SESSION, CREATE TABLE TO test_user;

11)Transportable datafiles

impdp directory=data_pump_dir dumpfile=test_data.dmp logfile=test_data_imp.log transport_datafiles=' /home/cybage/app/cybage/oradata/temp/test_data01.dbf'