Rollback segment problem

Quote:

---

Originally posted by abhaysk
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.

---

One cannot pedict the number of records to be deleted like that. The table is an OLTP and entries and contra-entries (DaPi has rightly conveyed the business logic) are being inserted online.Only those rows who have the contra entry(i.e., a negetive amount field) are to be deleted (which ranges from 5% to 40% at any given day).I cannot drop the original table as it is being used by other processes as well(For example TIBCO uses it for propagating the outstanding records)shuttding down these application may result in revenue loss.

Quote:

---

Originally posted by DaPi
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 DaPi, You are right.Commit between delete was a rahter bad idea.
I am sorry I acted like a "To be Oracle Scientist".
:D :D

I am doing something similar with tables containing data propagated by Tibco.

I have a table calls TISSI_ANUNCIO which contains the new data to be loaded into an Operational Data Store, this table is constantly being inserted by Tibco, what I have to do for each row inserted in ODS I must mark the movement as processed. What I have done is instead of open a cursor in TISSI_ANUNCIO and update this same table in the cursor is create another temporary table which calls TSILI_ANUNCIO, the structure of this table is simply the primary key of TISSI_ANUNCIO so I open the cursor in TISSI_ANUNCIO and insert the processed row´s primary key in TSILI_ANUNCIO then commit. Since I am not modifying TISSI_ANUNCIO my cursor does not suffer from snapshot too old. At end of the process I simply do

Code:

---

the flow is more or less like this

INSERT INTO TSILI_ANUNCIO
VALUES (:stAnulacion->szFeMovimiento,
        :stAnulacion->szCoContrato,
        :stAnulacion->iNuAnuncio,
        :stAnulacion->szCoProducto,
        :stAnulacion->szCoAmbiApa,
        SYSDATE);     

UPDATE        (
        SELECT        A.FE_TRATADO    A_FE_TRATADO    , B.FE_TRATADO    B_FE_TRATADO,
                A.CR_MOVIMIENTO A_CR_MOVIMIENTO
        FROM        TISSI_ANUNCIO A, TSILI_ANUNCIO B
        WHERE        A.CO_CONTRATO  = B.CO_CONTRATO
        AND        A.FE_MOVIMIENTO = B.FE_MOVIMIENTO
        AND        A.NU_ANUNCIO    = B.NU_ANUNCIO
        AND        A.CO_PRODUCTO  = B.CO_PRODUCTO
        AND        A.CO_AMBI_APA        = B.CO_AMBI_APA
        )
SET        A_CR_MOVIMIENTO = 'T',
        A_FE_TRATADO    = B_FE_TRATADO;

---

Yes.I am more or less doing the same thing using collection.

How many elements are you loading into the collection? You must store them all otherwise you may face the loop-delete-commit problem no?

Quote:

---

otherwise you may face the loop-delete-commit problem no

---

Sounds scary.
I am storing only the rowids.And no, I am not using any loop-delete-commit. Just one statement-- "Forall" and then commit--rollback in exceptions.

I mean how many rowids are you storing in your collection! If you dont store all you might get the loop-delete problem, if you store all you might get ORA-04030

Quote:

---

Originally posted by pando
I mean how many rowids are you storing in your collection! If you dont store all you might get the loop-delete problem, if you store all you might get ORA-04030

---

Yep !! Storing all the rowids.
ORA-04030??.No I don't think it will go down to that. This delete process will run daily. So, at the maximum, the collection may store about half a million rowids and the memory is capable of that:)

So the code is not bullet proof, there is always a possibility of facing ORA-04030.

Have you thought what happens if the process doesnt run for a week and you have to store 4 million rowid in a collection :)

Quote:

---

Originally posted by pando
So the code is not bullet proof, there is always a possibility of facing ORA-04030.

Have you thought what happens if the process doesnt run for a week and you have to store 4 million rowid in a collection :)

---

Not gonna happen the process will run as a daily job.
Btw 4 millions rows ain't nothin' for 8 gigs memory:D