fun with a package body

From: Patrice sur GMail <patrice.boivin_at_gmail.com>
Date: Mon, 10 Nov 2014 10:37:34 -0400
Message-ID: <CACH2EDLKL6J0RHp9X4xsgZAu+YphzRf5bxqESOJU=2iaM+ek=w_at_mail.gmail.com>

Did a trial migration using expdp and impdp of a schema.

one package body which includes remote references via synonyms + db links is valid in the original database but invalid in the new location.

Original version 10.2, new location 11.2.

With utlrp.sql or alter package compile body,the body refuses to compile due to ora-00980; synonym translation is no longer valid.

same synonyms in both
same db link DDL in both
same tnsnames.ora entries for both servers.

remote_dependencies_mode = TIMESTAMP

The timestamp differs however, in the original it is stamped with 2013 while in the newer database it is 2014.

Both databases are in noarchivelog mode and backed up on a weekly basis, i.e. the database of origin has shut down and restarted dozens of time since 2013.

How can Oracle claim that a package compiled successfully ('VALID')while its remote references are invalid? Does the "timestamp" setting mean that Oracle doesn't bother checking anything and compiles the package as long as the timestamp it has matches what it expects to see? Clearly it isn't validating the remote links at all.

• Patrice My profiles: [image: Facebook]
  <http://www.facebook.com/home.php?#!/profile.php?id=100000206805521>[image:
  LinkedIn] <http://ca.linkedin.com/pub/patrice-boivin/a/933/5a9>[image: Twitter] <http://www.twitter.com/PatriceBoivin>
  <http://www.twitter.com/PatriceBoivin>
  Signature powered by WiseStamp <http://www.wisestamp.com/email-install>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Nov 10 2014 - 15:37:34 CET