Re: db_link works for some databases, not others

Hmm ...

When attempting to merge to these misbehaving tables, Is there any chance that the host name, "devdb1", is not paired with an IP address?

Just shooting from the hip here

Regards,

Gus

On Wed, Jun 21, 2017 at 5:04 PM, Sandra Becker <sbecker6925_at_gmail.com> wrote:

> Oracle EE 12.1.0.2
>
> I have written a merge script to pull information from three tables and
> update when matched, insert when not matched into a master table over a
> database link. For 31 of my 35 databases, it works like a charm. The
> other 4 not so much. It is neither updating or inserting even though I get
> feedback like "99 rows merged". When I pull the SELECT statement out of
> the merge script, I see the correct values have been pulled. However,
> after the commit, no changes were applied to the master table. I also have
> run an update statement outside the script and successfully updated the
> master table. Obviously, something is wrong in my configurtion, but I
> haven't been able to pin it down. I verified the username/password on all
> the links is correct and works. I have compared tnsnames.ora entries on
> both the source and target servers and how the database link was created.
> The only difference I've seen was the UR = A in the tnsnames for the source
> tables host, but that applies to only 3 of the 4 databases where the script
> doesn't work. That was put in during the 12c upgrade several months ago.
> Where else would you suggest I look? What obvious thing have I overlooked?
>
> Thanks in advance for any suggestions.
>
> Creating the database link:
>
> CREATE DATABASE LINK devdb1
> CONNECT TO devdb1 IDENTIFIED BY <devdb1_pwd>
> USING 'DEVDB1';
>
> tnsnames entry on master table host:
>
> DEVDB1 =
> (DESCRIPTION =
> (ADDRESS = (PROTOCOL = TCP)(HOST = devhost1)(PORT = 1521))
> (CONNECT_DATA =
> (SERVER = DEDICATED)
> (SERVICE_NAME = DEVDB1)
> )
> )
>
> tnsnames entry on source tables host:
> DEVDB1 =
> (DESCRIPTION =
> (ADDRESS = (PROTOCOL = TCP)(HOST = devhost1)(PORT = 1521))
> (CONNECT_DATA =
> (SERVER = DEDICATED)
> (SERVICE_NAME = DEVDB1)
> (UR = A)
> )
> )
>
> merge script:
>
> MERGE INTO mstr_tracking t
> USING (SELECT
> m.db_username,
> m.ldap_username,
> m.email,
> m.date_disabled,
> m.db_name,
> m.ent_username,
> d.profile,
> u.ptime last_pwd_chg,
> d.expiry_date,
> d.created
> FROM emp_tracking_at_&&db_link m
> LEFT JOIN (SELECT u.name,u.ptime
> FROM sys.user$_at_&&db_link u
> ) u
> ON m.db_username = u.name
> LEFT JOIN (SELECT username, profile, expiry_date, created
> FROM dba_users_at_&&db_link d
> ) d
> ON m.db_username = d.username
> ) s
> ON (t.db_username = s.db_username AND
> t.db_name = s.db_name)
> WHEN MATCHED THEN UPDATE SET
> t.ldap_username = s.ldap_username,
> t.email = s.email,
> t.date_disabled = s.date_disabled,
> t.ent_username = s.ent_username,
> t.profile = s.profile,
> t.last_pwd_chg = s.last_pwd_chg,
> t.expiry_date = s.expiry_date,
> t.created = s.created
> WHEN NOT MATCHED THEN INSERT
> (
> t.db_username,
> t.ldap_username,
> t.email,
> t.date_disabled,
> t.db_name,
> t.ent_username,
> t.profile,
> t.last_pwd_chg,
> t.expiry_date,
> t.created
> )
> VALUES
> (
> s.db_username,
> s.ldap_username,
> s.email,
> s.date_disabled,
> s.db_name,
> s.ent_username,
> s.profile,
> s.last_pwd_chg,
> s.expiry_date,
> s.created
> );
> COMMIT;
> UNDEFINE db_link;
>
>
> --
> Sandy B.
>
>

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