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!
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:
Perform schema export from production server
Copy the dump files to test server
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
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.