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 don't 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.
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.
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 don't specify these options, imp
tries to create the objects under the same user
from the source database. If the user doesn't 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.
