Oracle Version : oracle 11g R2
What is Transportable Tablespaces(TTS)?
Transportable tablespaces were introduced in Oracle 8i to allow whole tablespaces to be copied between databases in the time it takes to copy the datafiles.Oracle 8i supports tablespace
transportation between databases that run on same OS platforms and use the same database block size. But in oracle 9i TTS(Transportable Tablespaces) technology was enhanced and was enabled
to use multiple block size which removed this restriction.In Oracle 10g, TTS (Transportable Tablespaces) technology was further enhanced to support transportation of tablespaces
between databases running on different OS platforms (e.g. Windows to Linux, Solaris to HP-UX), which has same ENDIAN formats.
Limitations on Transportable Tablespace :-
1. The source and target database must use the same character set and national character set.
2.Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects
are in the tablespace set.
3. You cannot transport the SYSTEM tablespace or objects owned by the user SYS. Thismeans that you cannot use TTS for PL/SQL, triggers, or views. These would have to be
moved with export.
4.You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the tablespace to be transported or
the destination tablespace before the transport operation.
5. You cannot transport an encrypted tablespace to a platform with different endianness.
Steps to transport tablespace from one database to another:-
1.Check endian format of both platforms,
Source DB:-
SQL> select platform_name from v$database
PLATFORM_NAME
-------------------------------------------------
Linux x86 64-bit
SQL> select * from v$transportable_platform where platform_name = 'Linux x86 64-bit' ;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------- --------------
13 Linux x86 64-bit Little
Destination DB:-
SQL> select platform_name from v$database
PLATFORM_NAME
----------------------------------------------------------
Linux x86 64-bit
SQL> select * from v$transportable_platform where platform_name = 'Linux x86 64-bit' ;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- -------------------------------------------- --------------
13 Linux x86 64-bit Little
Now let me create a tablespace to demonstrate the feature.
Source DB:-
[oracle@server1 ~]$ sqlplus / as sysdba
SQL> CREATE TABLESPACE tts DATAFILE '/u01/app/oracle/oradata/prim/tts_data01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M;
Tablespace created.
SQL> CREATE USER tts_user IDENTIFIED BY tts_user DEFAULT TABLESPACE tts TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON tts;
User created.
SQL> grant connect , resource , create table to tts_user;
Grant succeeded.
SQL> CREATE TABLE test_tbl (id NUMBER, description VARCHAR2(50),CONSTRAINT test_tbl_pk PRIMARY KEY (id) );
SQL> INSERT INTO test_tbl (id, description) SELECT level, 'Description for ' || level FROM dual CONNECT BY level <= 10000;
10000 rows created.
SQL> COMMIT;
Commit complete.
SQL> select count(*) from test_tbl;
COUNT(*)
----------
10000
Now For a tablespace to be transportable it must be totally self contained. This can be checked using the DBMS_TTS.TRANSPORT_SET_CHECK procedure.
SQL> CONN / AS SYSDBA
Connected.
SQL> EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'tts' , incl_constraints => TRUE);
PL/SQL procedure successfully completed.
P.S. You can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS view. If the setof tablespaces is self-contained, this view is empty.
SQL> select * from tRANSPORT_SET_VIOLATIONS;
no rows selected
Now , put the tablespace in read only mode which we are going to move.
SQL> alter tablespace tts read only;
Tablespace altered.
Now , Export the tablespace metadata using expdp utility.
[oracle@server1 ~]$ expdp system/system dumpfile=tts.dmp directory=DATA_PUMP_DIR TRANSPORT_TABLESPACES =tts
Export: Release 11.2.0.1.0 - Production on Thu Jan 21 14:59:52 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=ttss.dmp TRANSPORT_TABLESPACES=tts directory=DATA_PUMP_DIR
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/app/oracle/admin/prim/dpdump/tts.dmp
******************************************************************************
Datafiles required for transportable tablespace TTS:
/u01/app/oracle/oradata/prim/tts_data01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 15:00:57
Now transfer the datafile of the tablespace along with the dumpfile into the destination server. We will transfer it using scp .
[oracle@server1 prim]$ scp t/u01/app/oracle/oradata/prim/tts_data01.dbf oracle@192.168.2.104:/u01/app/oracle/oradata/prim/
oracle@192.168.2.104's password:
ts_data01.dbf 100% 10MB 10.0MB/s 00:00
[oracle@server1 prim]$ scp /u01/app/oracle/admin/prim/dpdump/tts.dmp oracle@192.168.2.104:/u01/app/oracle/admin/prim/dpdump/
tts.dmp 100% 96KB 96.0KB/s 00:00
Now the source tablespace can be switched back to read write mode.
SQL> alter tablespace tts read write;
Tablespace altered.
At Destination DB:-
Destination Database
Create any users in the destination database that owned objects within the tablespace being transported, assuming they do not already exist.
SQL> CONN / AS SYSDBA
SQL> CREATE USER tts_user IDENTIFIED BY tts_user;
User created.
SQL> GRANT connect , resource, create table TO tts_user;
Grant succeeded.
Import the dump file of transportable tablespace .
[oracle@server2 prim]$ impdp system/system directory=DATA_PUMP_DIR dumpfile=tts.dmp logfile=test_data_imp.log transport_datafiles='/u01/app/oracle/oradata/prim/tts_data01.dbf'
Import: Release 11.2.0.1.0 - Production on Thu Jan 8 23:56:26 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=DATA_PUMP_DIR dumpfile=tts.dmp logfile=test_data_imp.log transport_datafiles=/u01/app/oracle/oradata/prim/tts_data01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 23:56:31
SQL> select tablespace_name ,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TTS READ ONLY
SQL> alter tablespace tts read write;
Tablespace altered.
SQL> select TABLESPACE_NAME,PLUGGED_IN,status from dba_tablespaces where tablespace_name='TTS';
TABLESPACE_NAME PLU STATUS
------------------------------ --- ---------
TTS YES ONLINE
SQL> conn tts_user/tts_user
Connected.
SQL> select count(*) from test_tbl;
COUNT(*)
----------
10000
What is Transportable Tablespaces(TTS)?
Transportable tablespaces were introduced in Oracle 8i to allow whole tablespaces to be copied between databases in the time it takes to copy the datafiles.Oracle 8i supports tablespace
transportation between databases that run on same OS platforms and use the same database block size. But in oracle 9i TTS(Transportable Tablespaces) technology was enhanced and was enabled
to use multiple block size which removed this restriction.In Oracle 10g, TTS (Transportable Tablespaces) technology was further enhanced to support transportation of tablespaces
between databases running on different OS platforms (e.g. Windows to Linux, Solaris to HP-UX), which has same ENDIAN formats.
Limitations on Transportable Tablespace :-
1. The source and target database must use the same character set and national character set.
2.Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects
are in the tablespace set.
3. You cannot transport the SYSTEM tablespace or objects owned by the user SYS. Thismeans that you cannot use TTS for PL/SQL, triggers, or views. These would have to be
moved with export.
4.You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the tablespace to be transported or
the destination tablespace before the transport operation.
5. You cannot transport an encrypted tablespace to a platform with different endianness.
Steps to transport tablespace from one database to another:-
1.Check endian format of both platforms,
Source DB:-
SQL> select platform_name from v$database
PLATFORM_NAME
-------------------------------------------------
Linux x86 64-bit
SQL> select * from v$transportable_platform where platform_name = 'Linux x86 64-bit' ;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------- --------------
13 Linux x86 64-bit Little
Destination DB:-
SQL> select platform_name from v$database
PLATFORM_NAME
----------------------------------------------------------
Linux x86 64-bit
SQL> select * from v$transportable_platform where platform_name = 'Linux x86 64-bit' ;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- -------------------------------------------- --------------
13 Linux x86 64-bit Little
Now let me create a tablespace to demonstrate the feature.
Source DB:-
[oracle@server1 ~]$ sqlplus / as sysdba
SQL> CREATE TABLESPACE tts DATAFILE '/u01/app/oracle/oradata/prim/tts_data01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M;
Tablespace created.
SQL> CREATE USER tts_user IDENTIFIED BY tts_user DEFAULT TABLESPACE tts TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON tts;
User created.
SQL> grant connect , resource , create table to tts_user;
Grant succeeded.
SQL> CREATE TABLE test_tbl (id NUMBER, description VARCHAR2(50),CONSTRAINT test_tbl_pk PRIMARY KEY (id) );
SQL> INSERT INTO test_tbl (id, description) SELECT level, 'Description for ' || level FROM dual CONNECT BY level <= 10000;
10000 rows created.
SQL> COMMIT;
Commit complete.
SQL> select count(*) from test_tbl;
COUNT(*)
----------
10000
Now For a tablespace to be transportable it must be totally self contained. This can be checked using the DBMS_TTS.TRANSPORT_SET_CHECK procedure.
SQL> CONN / AS SYSDBA
Connected.
SQL> EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'tts' , incl_constraints => TRUE);
PL/SQL procedure successfully completed.
P.S. You can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS view. If the setof tablespaces is self-contained, this view is empty.
SQL> select * from tRANSPORT_SET_VIOLATIONS;
no rows selected
Now , put the tablespace in read only mode which we are going to move.
SQL> alter tablespace tts read only;
Tablespace altered.
Now , Export the tablespace metadata using expdp utility.
[oracle@server1 ~]$ expdp system/system dumpfile=tts.dmp directory=DATA_PUMP_DIR TRANSPORT_TABLESPACES =tts
Export: Release 11.2.0.1.0 - Production on Thu Jan 21 14:59:52 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=ttss.dmp TRANSPORT_TABLESPACES=tts directory=DATA_PUMP_DIR
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/app/oracle/admin/prim/dpdump/tts.dmp
******************************************************************************
Datafiles required for transportable tablespace TTS:
/u01/app/oracle/oradata/prim/tts_data01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 15:00:57
Now transfer the datafile of the tablespace along with the dumpfile into the destination server. We will transfer it using scp .
[oracle@server1 prim]$ scp t/u01/app/oracle/oradata/prim/tts_data01.dbf oracle@192.168.2.104:/u01/app/oracle/oradata/prim/
oracle@192.168.2.104's password:
ts_data01.dbf 100% 10MB 10.0MB/s 00:00
[oracle@server1 prim]$ scp /u01/app/oracle/admin/prim/dpdump/tts.dmp oracle@192.168.2.104:/u01/app/oracle/admin/prim/dpdump/
tts.dmp 100% 96KB 96.0KB/s 00:00
Now the source tablespace can be switched back to read write mode.
SQL> alter tablespace tts read write;
Tablespace altered.
At Destination DB:-
Destination Database
Create any users in the destination database that owned objects within the tablespace being transported, assuming they do not already exist.
SQL> CONN / AS SYSDBA
SQL> CREATE USER tts_user IDENTIFIED BY tts_user;
User created.
SQL> GRANT connect , resource, create table TO tts_user;
Grant succeeded.
Import the dump file of transportable tablespace .
[oracle@server2 prim]$ impdp system/system directory=DATA_PUMP_DIR dumpfile=tts.dmp logfile=test_data_imp.log transport_datafiles='/u01/app/oracle/oradata/prim/tts_data01.dbf'
Import: Release 11.2.0.1.0 - Production on Thu Jan 8 23:56:26 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=DATA_PUMP_DIR dumpfile=tts.dmp logfile=test_data_imp.log transport_datafiles=/u01/app/oracle/oradata/prim/tts_data01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 23:56:31
SQL> select tablespace_name ,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TTS READ ONLY
SQL> alter tablespace tts read write;
Tablespace altered.
SQL> select TABLESPACE_NAME,PLUGGED_IN,status from dba_tablespaces where tablespace_name='TTS';
TABLESPACE_NAME PLU STATUS
------------------------------ --- ---------
TTS YES ONLINE
SQL> conn tts_user/tts_user
Connected.
SQL> select count(*) from test_tbl;
COUNT(*)
----------
10000
