Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

expdp / impdp - Private synonyms using DB link not imported

Andreas HessJul 31 2012 — edited Jul 31 2012

Hi

I'd appreciate any help with this.

We are taking an export (expdp) of a database on Oracle EE 10.2.0.3 in Prod and then importing it into an identical DB on 10.2.0.5 in Dev.

I doubt the minor version difference is an issues, but I mention it for completeness.

Our expdp in prod used to look like this:
expdp agdba/x directory=DATA_PUMP_DIR dumpfile=X.DMP logfile=X.LOG schemas=A,B,C,D

We have changed it to:
expdp agdba/x directory=DATA_PUMP_DIR dumpfile=X.DMP logfile=X.LOG full=y EXCLUDE=SCHEMA:"IN ('E', 'F')"

( so basically do a full export, but exclude schemas E & F )

The impdp in dev has not changed (we import one schema at a time):

impdp agdba/x DUMPFILE=X.DMP LOGFILE=imp_X.LOG SCHEMAS=A DIRECTORY=DATA_PUMP_DIR TABLE_EXISTS_ACTION=SKIP

DB user AGDBA has been granted the DBA role...

The Issue:_

Private synonyms that use a DB link are NOT imported, but private synonyms that do NOT use a DB link ARE imported.

I can fix the issue by simply recreating the private synonyms using the DB link - e.g.:

CREATE SYNONYM EVENTLOGTBL FOR EVENTLOGTBL@FCISTOSMS;

Things were working fine until we change the expdp to use FULL. I see a few posts about synonyms and FULL=Y issues, but nothing quite like our problem.

Any ideas?

Thanks,
Andreas