Re: db_link works for some databases, not others

Figured out what was wrong after I stepped away from it for a couple of hours. The db_name in the master table didn't match the name for those 4 databases. Once I corrected the db_name, all databases are merging successfully. Thank you for reviewing my post.

Sandy

On Thu, Jun 22, 2017 at 11:10 AM, Mladen Gogala <gogala.mladen_at_gmail.com> wrote:

> On 06/21/2017 05:04 PM, Sandra Becker 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.
>
>
> Hi Sandy, are you getting any errors? What are the errors?
>
> Regards
>
>
> --
> Mladen Gogala
> Oracle DBA
> Tel: (347) 321-1217
>
>

-- 
Sandy B.

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