Re: db_link works for some databases, not others

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 <http://u.name>,u.ptime
> FROM sys.user$_at_&&db_link u
> ) u
> ON m.db_username = u.name <http://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


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