Import Transportable Tablespaces
Transportable tablespaces permit the copying or moving of a tablespace
from one Oracle database to another and are generally the fastest way
to move data between Oracle databases. That is because the
transportable tablespace process breaks down into two basic
operations: export/import the tablespace metadata from/to the
source/target database, and copy the tablespace operating system data
files. Note that one has to be granted the
imp_full_database privilege in order to load
Transportable tablespaces were introduced back with Oracle 8i, but
over the years they have incrementally improved quite a bit. So while
there are still some restrictions like the source and target database
must both be of the same character and national character sets, this
is one area where Oracle seems to make measurable improvements with
each and every release. So it will be best to check with the Oracle
version's documentation to verify the specific intra and inter version
limitations. So be keenly aware of all such issues before attempting.
Again, the complete transportable tablespace process steps are as
Check for cross platform issues, i.e. source & target endian formats
Check for cross tablespace dependencies (may define export filters)
Alter tablespace to READ ONLY mode
Export the transportable tablespace set, i.e. the tablespace metadata
Transfer both the export file and tablespace data files to the target
If necessary, convert data files to correct endian format for the
Import the transportable tablespace set, i.e. the tablespace metadata)
Alter tablespace to READ WRITE mode
Now examine in detail steps five through eight; the other steps are
covered by the previous section on exporting transportable
Step 5: Transfer both the export file and tablespace data files to the
This step is just as easy as it sounds. Simply copy or ftp the export
tablespace set and operating system level data files for that
tablespace from the source to the target. Remember to use binary mode
transfer with a favorite ftp program.
Step 6: Convert data files to correct endian format for the target
Back in Step 1, the Oracle endian cross-reference table as shown below
was checked so that the source database endiannes or byte order could
be recorded. Now check again for the target.
SQL> select * from V$TRANSPORTABLE_PLATFORM ;
1 Solaris[tm] OE (32-bit)
2 Solaris[tm] OE (64-bit)
7 Microsoft Windows IA (32-bit)
10 Linux IA (32-bit)
6 AIX-Based Systems (64-bit)
3 HP-UX (64-bit)
5 HP Tru64 UNIX
4 HP-UX IA
11 Linux IA (64-bit)
15 HP Open VMS
8 Microsoft Windows IA (64-bit)
9 IBM zSeries Based Linux
13 Linux 64-bit for AMD
16 Apple Mac OS
So if a tablespace is going to be transported from Windows to Linux,
there is no additional conversion step of the data file necessary. But
if the transport is happening from Solaris to Linux, the big endian
data file would need to be converted to little endian. The data format
conversion was postponed earlier because it is better to wait until
the format that is needed for the specific target de jour is apparent.
Once the target has been identified and it is discovered that a
conversion is necessary, then use the Oracle RMAN utility on the
target, or source, to reformat the data files with the proper byte
RMAN> convert datafile 'c:\temp\accounting.dbf'
2> to platform = "Linux 64-bit for AMD"
3> from platform = "HP Tru64 UNIX"
4> db_file_name_convert =
There are two items above of note. First, the data file was copied
into a temporary directory on the target server and not the file's
final destination. This is done because it is best not to move a data
file into the Oracle environment until it is ready to be absorbed,
which means the byte order has already been addressed. And second, the
convert was instructed about necessary file naming changes. In the
example above, this tablespace is moved from database ORDB2 to ORDB3,
so the data file headers need to be updated for that change.
Step 7: Import the transportable tablespace set
Now comes the easiest and most obvious part of the transportable
tablespace import process: the step to actually import the
tablespace(s) metadata. Also remember that it is just the metadata
being imported and not the actual data or data files. Hence, this step
is actually very quick and easy as shown here. If a good job was done
during step 2's check for being self-contained, then the parameter for
transport_full_check is probably just an extra, although
still worthwhile, step.
C:\> impdp bert/bert directory=data_pump_dir
Step 8: Alter transportable tablespace to READ WRITE mode
This is a fairly easy but usually mandatory step as the tablespace to
be must be placed into READ WRITE for the target database to actually
be able to utilize the objects and data contained within.
SQL> alter tablespace users read write;
If this step is skipped, the following error messages will occur:
ORA-00372: file 4 cannot be modified at this
ORA-01110: data file 4:
Get the Complete
Oracle Utility Information
The landmark book
Utilities The Definitive Reference" contains over 600 pages of
filled with valuable information on Oracle's secret utilities.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.