Transportable tablespaces

Here comes another wonderful feature in the new release of Oracle 8.1.5. Transportable tablespaces are a lot like cutting a piece of the database and pasting it to another database. Well, you dont need to actually cut it, you can always make a copy of the part of the database and add it to another database.

Introduction and Performing violation checks

The concept is to just copy the datafiles and move the dictionary information to the new server. Since you are exporting only the data dictionary information, the time required to move data between databases is much faster than using the regular import/export. Remember to connect as sys or sysdba while exporting or importing the data dictionary information.

The granularity, at which we can move pieces of a database, using this concept, is a tablespace. You can move as many tablespaces as you want in one shot. But the tablespaces must meet certain criteria.
· The tablespaces must be self-contained, meaning all the objects in the tablespace must be contained wholly in the tablespace
· The source and the target database must be on the same platform, you can move between sun & sun but not sun & NT.
· The source and target must have same database block size.
· The same tablespace name should not already be in use, by the target database.

You can check whether a tablespace is self-contained or not through a PL/SQL package DBMS_TTS. To check whether tablespaces TSP1 and TSP2 are in violation execute
DBMS_TTS.TRANSPORT_SET_CHECK(TSP1,TSP2, TRUE);

where TSP1, TSP2 are the tablespace names and TRUE specifies that the oracle server also take the constraints, foreign keys etc., into consideration. If it is FALSE the procedure does not bother about the constraints, and raises an error whenever you refer these objects in the destination database.

Now query the view TRANSPORT_SET_VIOLATIONS. This view lists all the objects and tablespace which is not self-contained. If there is no such tablespace, this view will be empty.
Export Tablespace Data Dictionary Info
Set the tablespaces to READ ONLY by issuing the following commands.
alter tablespace tsp1 read only;
alter tablespace tsp2 read only;

Now export the tablespaces using the export utility that is provided with the oracle server. This exports only metadata info pertaining to the tablespaces. Other options like Triggers=y, Grants=y and constraints=y can be used to copy the respective objects.

exp TRANSPORT_TABLESPACE=y TABLESPACES=tsp1,tsp2 FILE=export.dmp
set the tablespaces on the source database back to read write after the export.
alter tablespace tsp1 read write;
alter tablespace tsp2 read write;
Copy Files to New Location and Import Data Dictionary
Copy the export.dmp and the data files for these tablespaces into the destination server using any OS utility. On the destination database invoke the import utility to add the tablespaces to the new database. Once the files have been added, set the tablespaces to read write.

imp TRANSPORT_TABLESPACE=y DATAFILES=/d01/t1.dbf,/d01/t2.dbf TABLESPACES=tsp1,tsp2 FILE=export.dmp
alter tablespace tsp1 read write;
alter tablespace tsp2 read write;

If you want to change the ownership of the the objects, you can mention TOUSER and FROMUSER options. If you dont specify these options, imp tries to create the objects under the same user from the source database. If the user doesnt exists on the destination database, import will fail.
User Comments on this article

Jodie Mellinger writes... In the article, it lists certain requirements that the tablespace must meet. In my attempts to use transportable tablespaces, I have found another, undocumented requirement. The tablespace can not have any FUNCTION BASED INDEXES. I have not found this in any Oracle documentation, but when I run the check on my tablespace, I get the error " Domain/Functional Index VERTEXID.RATECITY in tablespace VERTEX not allowed in transportable set". The index specified does a DESC sort in the index. Just thought other users may benefit from this information.