I have to delete records which are older than 6 months in a audit table. The number of rows older than 6months is 40 million.
A direct delete is running out of rollback segment space, though I have added alot of space and I don't know if it is the right way to do it.
In order to issue commits, I have to use cursors. I think it will take forever to complete.
The option of creating a temporary table, truncating the audit table and inserting from the temp back to audit is my last option.
Can anyone suggest a easier and faster way to do this?
You want to distribute the deletes over many connections/transactions to reduce your freelists from becoming inbalanced. If you do not seperate the deletes one of your freelist's will become huge with old blocks and the others will be using mostly new blocks. So, you might want to create a PL/SQL procedure that uses automatous transactions or use a few SQL*Plus sessions using SQL, but spread out the load. If you are not using freelists then I am wasting my time.
______________________ Applications come and go,
but the data remains!