Thanks for the question, venkat.
Asked: September 28, 2018 - 7:00 am UTC
Last updated: September 28, 2018 - 9:30 am UTC
Version: 11g
Viewed 1000+ times
You Asked
Hi Tom,
iam facing a weird issue .
below is the scenario,
My package creates insert statements for 4 tables which lie on someother oracle 11g db.
tab1
tab2
tab3
tab4
The same 4 tables exists in 7 servers. (admin and cus1 to cu6).
admin
insert into tab1@dblink_for_admin (select --differnt vlues FROM DUAL
CONNECT BY LEVEL <= 140);
insert into tab2@dblink_for_admin (select FROM DUAL
CONNECT BY LEVEL <= 240);
insert into tab3@dblink_for_admin (select FROM DUAL
CONNECT BY LEVEL <= 180);
insert into tab4@dblink_for_admin (select FROM DUAL
CONNECT BY LEVEL <= 120);
cus1
insert into tab1@dblink_for_cus1 (select --differnt vlues FROM DUAL
CONNECT BY LEVEL <= 140);
insert into tab2@dblink_for_admin (select FROM DUAL
CONNECT BY LEVEL <= 240);
insert into tab3@dblink_for_admin (select FROM DUAL
CONNECT BY LEVEL <= 180);
insert into tab4@dblink_for_admin (select FROM DUAL
CONNECT BY LEVEL <= 120);
cus2:
insert into tab1@dblink_for_cus2 (select --differnt vlues FROM DUAL
CONNECT BY LEVEL <= 140);
insert into tab2@dblink_for_admin (select FROM DUAL
CONNECT BY LEVEL <= 240);
insert into tab3@dblink_for_admin (select FROM DUAL
CONNECT BY LEVEL <= 180);
insert into tab4@dblink_for_admin (select FROM DUAL
CONNECT BY LEVEL <= 120);
same condtions till cus6.
when the package got exuected.
tab1 , tab2,tab3 are corectly populated across all admin to cu6 (count and data matched).
tab4 is inserted with same select query which is from dual and we expect to popuate 120 rows in admin and cus1 to cus6.
but surprisingly admin is populated 120 rows at (12:00:08 seconds)
cus1 is populated with 120 rows at (12:00:08 seconds) + 1 row populated at (12:00:09 secnds).
cus2 is populated with 120 rows at (12:00:08 seconds) + 1 row populated at (12:00:10 seconds).
similar is the case till cus6.
expcept admin ,cus1 to cus6 is populated with an extra row ,that too with differnt time stamp of 120 set that actually populated in the server.
flow is sequential, admin next cus1 ,next cus2 like wise.
My problem is ,i can see data in tab4 in all servers for 120 rows at 12:00;08 seoncs
1 row hw it is getting porpulated and that too with differnt timestamp of seconds diffeernrce.
No loop back mechanism placed , and is happeing only in cus1 to cus6.
when manually exuected select statment i see 120 rows only...
i have checked any triggers at target db, and got to know no triggers.
Any help on this ,highly helpful to me.. and this is hapepnig in only prod env ..no getting replicated in any other env.
any pointers/suggestions would be of great help.
and Chris said...
Is this answer out of date? If it is, please let us know via a Comment