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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Export Transportable Tablespace tips

Oracle Database Tips by Donald Burleson

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

Transportable tablespaces permit the DBA to copy or move 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 the DBA does need to be granted the exp_full_database privilege in order to unload 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, e.g. 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. It will be best to check with the Oracle version's documentation to verify the specific intra- and inter-version limitations. Be keenly aware of all such issues before attempting.

 

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 one through four. The other steps are covered by the section on importing transportable tablespaces.

 

Step 1: Check for cross platform issues

 

All data at the lowest level has to have its bytes and bits formatted in some meaningful order when written to disk. This is referred to as endianness - or more simply, byte order. So while the in-memory value for 0x0A0B0C0D is always the same, how it is written to disk varies depending on hardware platform and operating system. The two alternatives are big and little endian format.

  

Figure 6.1:  Big Endian and Little Endian Format

 

Fortunately, Oracle keeps a nice endian cross-reference table for DBAs to utilize, which can be queried as shown below.

 

SQL> select * from V$TRANSPORTABLE_PLATFORM ;

 

PLATFORM_ID PLATFORM_NAME                    ENDIAN_FORMAT

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

          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 from Solaris to Linux, one would have to convert the big endian data file to little endian. For now, that conversion will be postponed until just before the import is implemented. Just record the source and target byte order natures so that one will know what, if anything, has to be done later.

 

Step 2: Check for cross tablespace dependencies

 

Transportable tablespaces must be self-contained. That means that the tablespace must stand entirely on its own with no logical or physical dependencies or references between objects in the transport set and anything outside that set. The four most common cross dependency issues include:

  • A referential integrity constraint such as a foreign key between a table inside the tablespace being transported and another table outside that tablespace

  • A partitioned object only partially contained within the tablespace being transported, i.e. object's partitions and/or sub-partitions span tablespaces

  • A table containing LOB columns that either reference or utilize another tablespace not in the export set. Examples: LOB storage, LOB overflow, and such

  • An index inside the tablespace being transported for a table in another tablespace that is outside the transport set

 Fortunately, once again Oracle offers features to make this step fairly painless via the DBMS-TTS package.  This package checks that a transport set is self contained.

 

DBMS_TTS.TRANSPORT_SET_CHECK (

   ts_list          IN CLOB,

   incl_constraints IN BOOLEAN DEFAULT FALSE,

   full_check       IN BOOLEAN DEFAULT FALSE);

 

Simply provide a tablespace name list separated by commas and two Boolean parameters. The first Boolean parameter is whether or not to check referential integrity constraints and this is usually set to TRUE. The second Boolean parameter indicates whether or not to perform a full check where full means anything either pointing into or out of the transport set. Once again, the recommended value is TRUE. So, of course, the defaults for both are FALSE!

 

This package is then executed and checked for its results via a SELECT against the transport_set_violations table as shown here. If "no rows selected" shows up, then it is good to go. Otherwise, it is best to record what the issues are and address them during the data pump export via the EXCLUDE clause.

 

SQL> exec dbms_tts.transport_set_check('ACCOUNTING',true,true)

SQL> select * from TRANSPORT_SET_VIOLATIONS;

no rows selected

Step 3: Alter transportable tablespace to READ ONLY mode

This is a fairly easy but quite mandatory step as the tablespace must be in READ ONLY mode for the export to succeed.

 

SQL> alter tablespace users read only;

Tablespace altered.

 

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

 

ORA-39123: Data Pump transportable tablespace job aborted

ORA-39185: The transportable tablespace failure list is

ORA-29335: tablespace 'USERS' is not read only

Job "BERT"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 11:13:22

Step 4: Export the transportable tablespace set

Now comes the easiest and most obvious part of the transportable tablespace export process: the step to actually export the tablespace(s) metadata. Remember, it is just the metadata being exported,  not the actual data or data files. Hence, this step is actually very quick and easy as shown here. Note that 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 generally still worthwhile, step. The data pump transportable tablespace export process has now been completed.

 

C:\> expdp bert/bert directory=data_pump_dir dumpfile=accounting.dmp transport_tablespaces=accounting transport_full_check=y


 

 

��  
 
 
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.