Transport Tablespace

SAMEER WADHWA

A Powerful feature introduced in Oracle 8i

Data movement has never been a pleasant experience especially when the database is very big and

the time is less. In this short article , I am explaining 8 simple steps for transport tablespace from one

database to another.

Summary :-

Step 1:- Check for self-contained tablespace(s).

Step 2:- Set the Tablespaces in read only mode.

Step 3:- Grab the information about data files belongs to desired tablespaces.

Step 4:- Export the tablespaces with options TRANSPORT_TABLESPACES=Y TABLESPACE = <TABLESPACE_NAME> TRIGGERS=N

Step 5:- Physical copy the tablespace datafiles from source to target location. Note that the data file names can be changed in this process.

Step 6:- Physical copy the export file, created in step 4 , from source to target system

Step 7:- Import the tablespace metadata into the target database ,using the names the data files were give in target system

Step 8:- The tablespace can be returned to read write mode on either or both nodes

Detail :-

Checklist before taking a decision for transporting a tablespace from source to target system

Answer it ?

Decision

Is transportable tablespace is self-contained ?

Yes

Can transport

Is transportable tablespace contains Nested tables, varrays, Bitmap indexes ?

Can transport

Are you willing to move part of data ?

Yes

Can not transport

Is source and target database have

Same operation system (OS)

Same Block size

Same Character set

Yes

Can transport

This checklist is valid up-to Oracle 8.1.7 . Some of the restriction like same oracle blocksize are not in Oracle 9i. In 9i if you are transporting a tablespace from different version of oracle databases , you have to set db cache_size. (Refer oracle doc)

Step 1:- Check for self-contained tablespace(s).

For checking of self-contained tablespaces we will use package DBMS_TTS.TRANSPORT_SET_CHECK package. This package is created by dbmsplts.sql which ran by catproc.sql and populate TRANSPORT_SET_VIOLATIONS table.

Svrmgrl>EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(TS_LIST=>'<TABLESPACE1>,<TABLESPACE2',incl_constraints=><FALSE/TRUE>);

As you see this package mainly consist of two input parameters

1. Transportable tablespace name

2. Whether you want to include the constraints or not.

For the sake of simplicity let us consider a scenario where we need to transport two tablespaces STablespace1 and STablespace2 from source to destination database across a network. Assume that both the database are on windows NT , Oracle database version is 8.1.6.3.0 , same Database block size and same charcter set.

Connect with sys and execute the package as follows :-

SQL> execute dbms_tts.transport_Set_check(TS_LIST=>('STablespace1,STablespace2'),incl_constraints=>TRUE);

PL/SQL procedure successfully completed.

If the tablespace is not self-contained then the above package will popluate transport_set_violation table. Query this table as follows to check violation.

SQL> select * from transport_set_violations;

no rows selected

If it returns "no rows selected" means tablespaces are ready for transportation. Otherwise part of the tablespace objects are linked with other tablespace . You have to make these tablespaces as self contained first.

Step 2:- Set the Tablespaces in read only mode.

SQL> alter tablespace SourceTablespace1 read only;

Tablespace altered.

SQL> alter tablespace SourceTablespace2 read only;

Tablespace altered.

Step 3:- Grab the information about data files belongs to desired tablespaces.

SQL> desc dba_data_files;

Name Null? Type

----------------------------------------- -------- -----------------

FILE_NAME VARCHAR2(513)

FILE_ID NUMBER

TABLESPACE_NAME VARCHAR2(30)

BYTES NUMBER

BLOCKS NUMBER

STATUS VARCHAR2(9)

RELATIVE_FNO NUMBER

AUTOEXTENSIBLE VARCHAR2(3)

MAXBYTES NUMBER

MAXBLOCKS NUMBER

INCREMENT_BY NUMBER

USER_BYTES NUMBER

USER_BLOCKS NUMBER

SQL> select tablespace_name,file_name from dba_Data_files

2 where tablespace_name in ('SourceTablespace1','SourceTablespace2');

TABLESPACE_NAME FILE_NAME

-------------------- ---------------------------------------------------------------------

SourceTablespace1 F:\OR8I\ORADATA\SourceTablespace11ORSV.DBF

SourceTablespace2 F:\OR8I\ORADATA\INDX01ORSV.DBF

Step 4:- Export the tablespaces with options TRANSPORT_TABLESPACES=Y TABLESPACE = <TABLESPACE_NAME> TRIGGERS=N

C:\>set ORACLE_SID=SAMDB

C:\>exp 'sys/change_on_install as SYSDBA' Tablespaces=('SourceTablespace1','SourceTablespace2') TRANSPORT_TABLESPACE=Y CONSTRAINTS=N file=expdat.dmp

Export: Release 8.1.6.3.0 - Production on Thu May 3 11:20:50 2001

Connected to: Oracle8i Enterprise Edition Release 8.1.6.3.0 - Production

With the Partitioning option

JServer Release 8.1.6.3.0 - Production

Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set

Note: table data (rows) will not be exported

About to export transportable tablespace metadata...

For tablespace SourceTablespace1 ...

. exporting cluster definitions

. exporting table definitions

. . exporting table SAMTEST1

. . exporting table SAMTEST2

. . exporting table SAMTEST3

. . exporting table SAMTEST4

. . exporting table SAMTEST5

. . exporting table SAMTEST6

. . exporting table SAMTEST7

. . exporting table SAMTEST8

. . exporting table SAMTEST9

For tablespace SourceTablespace2 ...

. exporting cluster definitions

. exporting table definitions

. exporting referential integrity constraints

. exporting triggers

. end transportable tablespace metadata export

Export terminated successfully without warnings.

Step 5:- Physical copy the tablespace datafiles from source to target location. Note that the datafile names can be changed in this process.

c:\>copy F:\OR8I\ORADATA\SourceTablespace11ORSV.DBF ...... <target system>

c:\>copy F:\OR8I\ORADATA\INDX01ORSV.DBF .......... <target system>

Step 6:- Physical copy the export file, created in step 4 , from source to destination location.

c:\> copy expdat.dmp ........... <target system>

On unix , you can ftp the files with binary .

Step 7:- Import the tablespace metadata into the target database ,using the names the datafiles were give in target system

C:\>imp sys/mantra file=c:\temp\expdat.dmp transport_tablespace=Y datafiles=('E:\ORA816\DATABASE\SourceTablespace11ORSV.DBF','E:\ORA816\DATAB

ASE\INDX01ORSV.DBF')

Import: Release 8.1.6.3.0 - Production on Thu May 3 12:23:03 2001

Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production

With the Partitioning option

JServer Release 8.1.6.3.0 - Production

Export file created by EXPORT:V08.01.06 via conventional path

About to import transportable tablespace(s) metadata...

import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set

. importing SYS's objects into SYS

importing tables ...

About to enable constraints...

. importing SourceTablespace1's objects into SourceTablespace1

Import terminated successfully without warnings.

Step 8:- The tablespace can be returned to read write mode on either or both nodes

Sql> alter tablespace SourceTablespace1 read write;

Sql> alter tablespace SourceTablespace2 read write;

Conclusion : These are the 8 simple steps to understand the functionality of transportable tablespace. Any question or suggestion are most welcome.

Feedback GuestBook

Sameer Wadhwa