oracle transportable tablespace - ghdrako/doc_snipets GitHub Wiki

Transportable Tablespace (TTS)

Create test tablespace to tts on source

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;
  • check for any transport violations
EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'IQS_TBS', incl_constraints => TRUE);
SELECT * FROM transport_set_violations;
  • switching tablespace to read only mode
ALTER TABLESPACE IQS_TBS READ ONLY;
  • use data pump for performing tablespace transport, create export directory
CREATE OR REPLACE DIRECTORY TTS_DIR AS '/tmp';
  • Export the tablespace metadata using EXPDP
expdp directory=tts_dir transport_tablespaces=IQS_TBS dumpfile=IQS_data.dmp logfile=IQS_data.log
  • 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/';
  • 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.