rollback chocking up ... why? cursor to blame??
Date: Tue, 22 Jun 1999 07:12:03 GMT
Message-ID: <7knd05$c34$1_at_kraken.itc.gu.edu.au>
HiYa
[Quoted] I am trying to insert into database X from database Y across sqlnet. I am wanting to get records from table AAA on X and insert these into AAA on Y, the definitions of the tables are the same.
As I have dropped the indexes to make the process faster I wanted to ensure that I didn't bring in any that would preclude a unique index on 4 of the columns, I chose a corelated subquery.
I have the process driven by a cursor, and run it on the Y instance:
cursor CUR_INSERT is
select *
from AAA_at_X a
[Quoted] where not exists (select 'x'
from AAA b
where b.IND_1 = a.IND_1
and b.IND_2 = a.IND_2
and b.IND_3 = a.IND_3
and b.IND_4 = a.IND_4);
[Quoted] then there is a loop on the cursor, with a commit every 5000 rows
IE
for c_ins_rec in CUR_INSERT loop
LINE_CNT := LINE_CNT + 1 ;
insert into AAA
values (
c_ins_rec.blah
);
if LINE_CNT = 5000 then
LINE_CNT := 0;
commit;
end if;
end loop;
commit;
END;
/
I would have thought that this would _NOT_ clogg up the rollback space, and would move along fine.
Is this correlation back into the table being inserted into going to effect the operation of the cursor???
See Ya
(when bandwidth gets better ;-)
Chris Eastwood
Photographer, Programmer email ua.ude.ug.xobliam_at_doowtsae.c
Motorcyclist and dingbat
please remove undies for reply
Ohhh ... you work all day, slave over a hot stove all night yet you *still* have time for sadomasochism ... how do you do it? A little man hurts me.
<ding>
Oh Mister Mean ... Oh Mister Mean
15 Minutes with him and you'll be blue and green
Studded belts and leather whips
Bondage trousers on his hips
Wack and punish as you dust with Mister Mean
Ohhhh he whips me around the house in minutes ... lets get cracking ...
Received on Tue Jun 22 1999 - 09:12:03 CEST