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.
