Skip to Main Content
  • Questions
  • Insertion over db links creating extra rows than expected

Breadcrumb

May 4th

Question and Answer

Chris Saxon

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...

Well, the obvious answer is someone else inserts the extra row...

Assuming you've ruled that out, trace your session to see exactly what's happening. You can trace local activity with:

alter session set sql_trace = true;

... your code ...

alter session set sql_trace = false;


To see what's happening on the remote database, you need to create a procedure to start tracing on the remote database. And call that. See:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9538429800346241733

For details on how to find and parse the trace files, see:

https://blogs.oracle.com/sql/how-to-create-an-execution-plan#tkprof

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database