Rollback segment problem

Quote:

---

Originally posted by DaPi
If I'm right about your business needs, you're deleting pairs of matching entries (transactions?):

1) The cursor "selects" 8mio rows including a pair +123 and -123

2) You loop & commmit several times, and by chance this deletes the row +123 & commits, by chance -123 hasn't be processed yet.

3) It crashes with a 1555

4) You restart. The cursor is re-evaluated and "selects" 7mio rows BUT DOES NOT INCLUDE -123 because this is not a paired entry any more; its matching entry no longer exists.

BINGO - your books don't balance

---

Yeah you are right.That's why I am doing it on a cloned table instead of the original one.After the procedure is succesful, I will run it on the original table.

Quote:

---

Originally posted by gandolf989
Let's just be friends! ;)

---

You don't want to make Mr Hanky angry do you??
:D :D :D

Quote:

---

Originally posted by simply_dba
After the procedure is succesful, I will run it on the original table.

---

The problem is that a 1555 will depend on the work-load, db configuration etc. So a successful test-run does NOT guarantee success in production.

Quote:

---

Originally posted by DaPi
The problem is that a 1555 will depend on the work-load, db configuration etc. So a successful test-run does NOT guarantee success in production.

---

That's true. but now I have developed a procedure to delete using collection ( A DBA doing a Developer's work.:rolleyes: I wonder what Mr Hanky will say) storing all the rowids in another table.I think this ay I can ensure data consistency. Even if I ran into 1555.

Quote:

---

Originally posted by simply_dba
. . .but now I have developed a procedure to delete using collection . . . . storing all the rowids in another table.

---

Sounds good to me.

cant you do smth like this?

Code:

---


loop
Delete from temp_tab a where exists
(select 1 from temp_tab b where ((a.amount>0
and b.AMOUNT<0) or(a.amount<0 and b.AMOUNT>0))
and abs(a.amount)=abs(b.AMOUNT)
and a.md=b.MD
and a.comment=b.COMMENT
and a.type=b.TYPE)
and rownum <= 100000;
commit;
exit when sql%rowcount < 100000;
end loop;

---

Quote:

---

Originally posted by pando
cant you do smth like this?

---

NO ! ! ! !

If my reading is right (simply_dba hasn't said otherwise) the requirement is to delete pairs of matching rows. This technique could delete one of a pair in the first loop and would NOT select the second in subsequent loops beacuse it no longer matches.

yes

it might work if the number of matching rows is not bigger than 100000 rows correct?

Quote:

---

Originally posted by pando
yes

it might work if the number of matching rows is not bigger than 100000 rows correct?

---

If it's less than 100000 you don't need a loop.
If it's more than 100000 you risk your data integrity.

I wonder how no one asked.... wat is the number of records you want to retain?

If you are trying to delete more than 25% of records from a table.. go for parallel insert into temp table the records you want to retain.. drop main table.. rename temp table to main table..

Abhay.