Expdp schema export is missing tables

Gunnar P

Hi everyone,

I am trying to implement a schema export with expdp on 12.1.0.2 (Windows7, 64bit) with the following call:

"EXPDP usr/[email protected] SCHEMAS=XXX DIRECTORY=TMP_EXPDP VERSION=10.2.0.1.0 EXCLUDE=STATISTICS dumpfile=tt.sad logfile=tt.log"

In the resulting logfile I can see that at least 2 Tables are missing??

Is there anyone who can explain that behaviour?

Saubhik

Does the excluded tables contains virtual columns or LOBs (archived), in other words some features that is not available in 10.2

Richard Harrison .

Hi,

I had something similar and it was that compatible was not set to 12.1.0.2 at the database level (i.e. the init/spfile). It was still at an earlier version and had not been changed during an earlier upgrade process.

Perhaps check that?

Cheers,

Rich

Gunnar P

Hi,

thanks for your fast reply.

The compatibility parameter is set, so that can't be the reason

Greets

Saubhik

Does the excluded tables contains virtual columns or LOBs (archived), in other words some features that is not available in 10.2

Gunnar P

Hi,

no, neither the table nor the database has been changed since it ran on a Oracle 10 Server. The database was only migrated to 12c and to be backward compatible we have looked for a way to exchange data between those instances.

Greets

Richard Harrison .

ACtually most likely thing is this - it only happened to me a few weeks ago and I'd almost forgotten 0 but luckily i wrote it up in a blog note - the problem is probably extended stats - see here

Oracle DBA Blog 2.0: The datapump detective...

Cheers,

Rch

Gunnar P

Thanks to your thoughts i have found the root of evil on this problem

It's because of virtual columns( as Saubhik suggested) on that tables that weren't exported.

I don't know how and who created them but why is the expdp tool not complaining about this error.

I had expected that when i want to export a schema for a specific version and not the whole schema fits into that version an error should raise or at least should be logged.

Greets

Dean Gagne-Oracle

Hi,

So when you export using the version parameter and you are using a lower version than current, Data Pump just uses older views to it only grabs objects that the older version can import.  The views are defined to ignore objects it knows the older database versions can't import.  Because everything is buried in the view, no code knows that tables, indexes, etc, are not being exported.  It has done this since the old exp/imp days.

Dean