Forum Stats

  • 3,826,921 Users
  • 2,260,725 Discussions
  • 7,897,122 Comments

Discussions

Expdp schema export is missing tables

Gunnar P
Gunnar P Member Posts: 4

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?

Tagged:

Best Answer

Answers

  • Richard Harrison .
    Richard Harrison . Member Posts: 2,065 Gold Trophy
    edited Sep 16, 2015 4:02AM

    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
    Gunnar P Member Posts: 4
    edited Sep 16, 2015 5:16AM

    Hi,

    thanks for your fast reply.

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

    Greets

  • Saubhik
    Saubhik Member Posts: 5,803 Gold Crown
    edited Sep 16, 2015 7:09AM Answer ✓

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

  • Gunnar P
    Gunnar P Member Posts: 4
    edited Sep 16, 2015 7:20AM

    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 .
    Richard Harrison . Member Posts: 2,065 Gold Trophy
    edited Sep 16, 2015 8:12AM

    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
  • Gunnar P
    Gunnar P Member Posts: 4
    edited Sep 17, 2015 6:06AM

    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
    Dean Gagne-Oracle Member Posts: 2,231 Employee
    edited Sep 17, 2015 9:34AM

    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

This discussion has been closed.