How to Transport Tablespace in Oracle Database?

Transport Tablespaces in Oracle Database 11g

How to transport a tablespace in Oracle database | Steps to transport a tablespace | Manual guide on how to transport a tablespace | how to check if a tablespace is transportable or not

Transport Tablespaces in Oracle Database 11g

You can use the transportable tablespaces feature to move a subset of an Oracle Database and "plug" it in to another Oracle Database, essentially moving tablespaces between the databases. The tablespaces being transported can be either dictionary managed or locally managed. Starting with Oracle9i, the transported tablespaces are not required to be of the same block size as the target database standard block size.

In this article, I have shown how to accomplish the same. Please follow the below mentioned steps to get to know how to do it.

Step 1: Create a new tablespace, a separate user for that particular tablespace. Also provide some basic privileges to the new user.

SQL> create tablespace TRANS_TAB datafile 'F:\DATABASE\ORADB11G\TRANSTAB01.DBF' size 1M autoextend on;
Tablespace created.
SQL> create user TEST_TAB identified by test default tablespace TRANS_TAB quota unlimited on TRANS_TAB;
User created.
SQL> grant create session, resource, create table to TEST_TAB;
Grant succeeded.

Step 2: Login with new user, create a table and insert some values in that table. (This is not necessary but can help with validation if needed since it's a new tablespace with no data).

SQL> conn TEST_TAB@oradb11g
Enter password:
Connected.
SQL>
SQL> create table DEMO_TABLE(id number, description varchar2(20));
Table created.
 
insert into DEMO_TABLE values (1, 'qwerty');
insert into DEMO_TABLE values (2, 'qwerty1');
insert into DEMO_TABLE values (3, 'qwerty13');
insert into DEMO_TABLE values (4, 'qwerty132');
insert into DEMO_TABLE values (5, 'qwerty1324');
insert into DEMO_TABLE values (6, 'qwerty13245');
commit;
 
SQL> set lines 200
SQL> select * from DEMO_TABLE;
        ID DESCRIPTION
---------- --------------------
         1 qwerty
         2 qwerty1
         3 qwerty13
         4 qwerty132
         5 qwerty1324
         6 qwerty13245
6 rows selected.

Step 3: Before transporting the tablespace, check for any violations because for a tablespace to be transportable, it must be totally self contained.

SQL> conn sys@oradb11g as sysdba
Enter password:
Connected.
SQL>
SQL> EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TRANS_TAB', incl_constraints => TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM transport_set_violations;
no rows selected

Step 4: Create the directory for Data Pump Export and put the Tablespace in Read Only mode.

SQL> alter tablespace TRANS_TAB read only;
Tablespace altered.
SQL> create directory DB_DUMP as 'F:\Database\DUMP';
Directory created.

Step 5: Exporting the tablespace Metadata using EXPDP.

C:\Users\parain>expdp directory=DB_DUMP transport_tablespaces=TRANS_TAB dumpfile=trans_tab.dmp logfile=trans_tab.log
Export: Release 11.2.0.4.0 - Production on Thu May 10 17:21:44 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Username: system@oradb11g
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/********@oradb11g directory=DB_DUMP transport_tablespaces=
TRANS_TAB dumpfile=trans_tab.dmp logfile=trans_tab.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
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:
  F:\DATABASE\DUMP\TRANS_TAB.DMP
******************************************************************************
Datafiles required for transportable tablespace TRANS_TAB:
  F:\DATABASE\ORADB11G\TRANSTAB01.DBF
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Thu May 10 17:22:35 2018 elapsed 0 00:00:28

Step 6: Before Putting the tablespace back again to READ WRITE Mode, copy the datafile of that tablespace to target database. OR if you copy the datafile when tablespace is in READ WRITE mode, you’ll be getting Oracle Error ORA-19722.

Step 7: Once datafile copying is done, put that Tablespace back in READ WRITE Mode.

C:\Users\parain>sqlplus sys@oradb11g as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 10 17:23:50 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter tablespace TRANS_TAB read write;
Tablespace altered.

Step 8: Connect to TARGET database, create the Data Pump Directory and New User (as created in the SOURCE database)

C:\Users\parain>sqlplus sys@oradb112 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 10 17:24:26 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create directory DB_DUMP as 'F:\Database\DUMP';
Directory created.
 
SQL> create user TEST_TAB identified by test;
User created.
SQL> grant create session, resource, create table to TEST_TAB;
Grant succeeded.

Step 9: Import the Tablespace Metadata and mention the copied datafile in the TRANSPORT_DATAFILE parameter.

C:\Users\parain>impdp directory=DB_DUMP dumpfile=TRANS_TAB.DMP logfile=impTRANSTAB.log transport_datafiles=
'F:\DATABASE\ORADB112\TRANSTAB01.DBF'
Import: Release 11.2.0.4.0 - Production on Thu May 10 17:39:57 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Username: system@oradb112
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.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/********@oradb112 directory=DB_DUMP dumpfile=TRANS_TAB.DMP 
logfile=impTRANSTAB.log transport_datafiles='F:\DATABASE\ORADB112\TRANSTAB01.DBF'
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Thu May 10 17:40:09 2018 elapsed 0 00:00:02

Step 10: Check for the Tablespace Entry in the DBA_TABLESPACES view and change it’s status to Read Write Mode.

C:\Users\parain>sqlplus sys@oradb112 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu May 10 17:40:27 2018
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TRANS_TAB                      READ ONLY
6 rows selected.
SQL> alter tablespace TRANS_TAB read write;
Tablespace altered.
SQL> select tablespace_name, status from dba_tablespaces;
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TRANS_TAB                      ONLINE
6 rows selected.

I hope this helps !!