Oracle Transportable Tablespace

Assume that you have a schema (IQS) on production server and you want to import the schema into test server. The ideal approach would be to perform entire schema export and import using data pump. Right!

• Transportable Tablespace (TTS)

• TTS Activity

• Copy Files to Target Server

• On Target Server

• Limitation of TTS

Let us add some complexity – What if IQS schema size is 200 GB and all of IQS schema objects reside under dedicated tablespace IQS_Tbs?

If you again go with data pump method, the steps involved are

1. Perform schema export from production server

2. Copy the dump files to test server

3. Perform schema import on test server

These steps will take lot of time. Instead, you can use transportable tablespace method to move tablespace from one database to another database. You can even move tablespaces between databases residing on different operating system platforms.

Transportable Tablespace (TTS)

Ultimately, you can move large amounts of data between databases just simply by moving data file from one database to another database. There are lot of real time context where TTS is used

• Archiving historical data into separate database

• Consolidate data from multiple databases into one database

• Moving data from OLTP database into data warehouse

• Schema replication when entire schema objects are contained in one single tablespace

TTS Activity

For activity purpose, I am creating a new tablespace and user on source database

CREATE TABLESPACE IQS_TBS DATAFILE '/u01/app/oracle/oradata/proddb/test_data01.dbf' 
SIZE 1M;

CREATE USER IQS IDENTIFIED BY iqs DEFAULT TABLESPACE IQS_TBS
TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON IQS_TBS;

GRANT CREATE SESSION, CREATE TABLE TO IQS;

GRANT SELECT ON HR.EMPLOYEES TO IQS;

CONN iqs/iqs

CREATE TABLE TEST_EMP AS SELECT * FROM HR.EMPLOYEES;

We are going to perform IQS_TBS tablespace transport from prod server to test server. But before transporting, we need to check for any transport violations.

Execute below procedure and then query TRANSPORT_SET_VIOLATIONS view for any violations

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

PL/SQL procedure successfully completed.

SELECT * FROM transport_set_violations;

no rows selected

Make sure there are no violations. If found, fix it and then proceed by switching tablespace to read only mode

SQL> ALTER TABLESPACE IQS_TBS READ ONLY;

Tablespace altered.

As we are going to use data pump for performing tablespace transport, create export directory

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

Export the tablespace metadata using EXPDP. This will only export tablespace meta information into dumpfile

expdp directory=tts_dir transport_tablespaces=IQS_TBS dumpfile=IQS_data.dmp logfile=IQS_data.log

Once export is done, switch the tablespace back to READ/WRITE mode

ALTER TABLESPACE IQS_TBS READ WRITE;

Copy Files to Target Server

Make sure you are copying below files on test server

• Copy the IQS_TBS tablespace data file /u01/app/oracle/oradata/proddb/test_data01.dbf on target server /u01/app/oracle/oradata/testdb/test_data01.dbf

• Copy the dump file IQS_data.dmp to test server /tmp location

On Target Server

Create a new user on test server which will own all the objects inside the tablespace that we are transporting. Create user only if its not already available on test server.

CREATE USER IQS IDENTIFIED BY iqs;

GRANT CREATE SESSION, CREATE TABLE TO IQS;

Create import directory inside database

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

Now import the tablespace metadata using IMPDP

impdp directory=temp_dir dumpfile=IQS_data.dmp logfile=import.log 
transport_datafiles='/u01/app/oracle/oradata/testdb/test_data01.dbf';

Switch the new tablespace into read/write mode on test server

ALTER TABLESPACE IQS_TBS READ WRITE;

Your tablespace is now available on test server as well!!

SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME='IQS_TBS';

Limitation of TTS

• Cannot transport System, Sysaux, Temp or Undo tablespaces

• Source and target must have same character set

• If ASM is used on source or on target, use RMAN to transport / convert the tablespace

• Cannot transport if you have tablespace with same name on target

• You must have same schema name on target as on source. If you are transporting tablespace owned by IQS schema on source, so you must have IQS schema on target.