Oracle Exadata Hardware
 
Oracle Exadata Storage Server Software
 
Exadata Database Machine X2-2 Hardware
 
Exadata Database Machine V2
 

PLA-Support>Database Technology>Engineered Systems>Oracle Exadata>DB: Exadata_EST
 
.Old GCS Categories>ST>Server>Manageability>Utilities>ExportImport>Usage - Transfer Data to Different Database
 

Transportable tablespace is a commonly used method for quickly moving large amounts of data from one database to another when the Endianess matches:
In this problem the user encountered different results or more importantly _no_ results after performing TTS. A difference was detected in the result set depending on
using INDEXes or No Indexes

Applies to:

Symptoms

• SQL statement returns no-rows on a Partition table when the row actually exists. When forcing a full scan the rows comes back.
• The table had recently been transported to a new database using TTS
• The same query with the same tablespace and data had previously worked before using TTS
• A difference was found in the results if using a Full table scan ( FTS = worked) vs. using an index scan (broken)

Changes

•  TTS used to move data from one database to a second.
•  No errors or problems seen during the transport
•  New database

Not changed

•  Same query previously used had worked and retrieved rows
•  Same data set previously used
•  Same Schema and object used

Cause

A closer analysis revealed that the data was actually inserted in an unexpected partition.

This alters the problem context:

Rather than a problem with wrong results which is typically a SQL / CBO based problem finding
data in the wrong partition would be a result of the Exchange (TTS)

Closer investigation of the method used revealed that the user had performed the TTS with

... ' INCLUDING INDEXES WITHOUT VALIDATION '

Solution

Retry the partition exchange with Index validation (default) -- no extra syntax required to enable this during TTS


We do suggest running ANALYZE TABLE <tablename>  VALIDATE STRUCTURE CASCADE;

Last: drop and recreate the indexes or if necessary, reload incorrect or outlying data back into the proper partitions

 
Comment: if the above also fails after validating the indexes please open an SR with Oracle Support Services
so we will look at your problem more closely
 

This solution has no attachment