Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Importing Transportable Tablespace Tips

Oracle Database Tips by Donald Burleson

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.


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 follows:


1.      Check for cross platform issues, i.e. source & target endian formats

2.      Check for cross tablespace dependencies (may define export filters)

3.      Alter tablespace to READ ONLY mode

4.      Export the transportable tablespace set, i.e. the tablespace metadata

5.      Transfer both the export file and tablespace data files to the target

6.      If necessary, convert data files to correct endian format for the target

7.      Import the transportable tablespace set, i.e. the tablespace metadata)

8.      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 tablespaces.


Step 5: Transfer both the export file and tablespace data files to the target


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.





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

          1 Solaris[tm] OE (32-bit)          Big

          2 Solaris[tm] OE (64-bit)          Big

          7 Microsoft Windows IA (32-bit)    Little

         10 Linux IA (32-bit)                Little

          6 AIX-Based Systems (64-bit)       Big

          3 HP-UX (64-bit)                   Big

          5 HP Tru64 UNIX                    Little

          4 HP-UX IA (64-bit)                Big

         11 Linux IA (64-bit)                Little

         15 HP Open VMS                      Little

          8 Microsoft Windows IA (64-bit)    Little

          9 IBM zSeries Based Linux          Big

         13 Linux 64-bit for AMD             Little

         16 Apple Mac OS                     Big


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 order encoding.


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 =

5> "c:\oracle\oradata\ordb2\",

6> "c:\oracle\oradata\ordb3\"

7> parallelism=2;


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 dumpfile=accounting.dmp transport_tablespaces=accounting transport_full_check=y

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;

Tablespace altered.


If this step is skipped, the following error messages will occur:


ORA-00372: file 4 cannot be modified at this time

ORA-01110: data file 4: 'C:\ORACLE\ORADATA\ORDB2\USERS.DBF'

Get the Complete
Oracle Utility Information 

The landmark book "Advanced Oracle 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 buy it for 30% off directly from the publisher.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.