delete script [message #35685] |
Fri, 05 October 2001 08:38 |
abdul
Messages: 18 Registered: May 2001
|
Junior Member |
|
|
I am attempting to delete 20+ mill records from numerous tables in a pl/sql script. I am getting rollback segment errors even after a committing after every 1000 records and increasing the segment size. How can I resolve the problem in a simple fast way?
----------------------------------------------------------------------
|
|
|
Re: delete script [message #35686 is a reply to message #35685] |
Fri, 05 October 2001 09:14 |
Suresh Vemulapalli
Messages: 624 Registered: August 2000
|
Senior Member |
|
|
create one big rollback segment and set transaction to use that rollback segemtn and try.
set transaction use rollback segment bigrollbackseg
SURESH
----------------------------------------------------------------------
|
|
|
Re: delete script [message #35690 is a reply to message #35685] |
Fri, 05 October 2001 17:08 |
Jim Willis
Messages: 11 Registered: October 2001
|
Junior Member |
|
|
1) What is the error your receiving - exactly
2) is this a one time process or ongoing
3) can you execute this in a shell script
4) Yes, you can create a very large rollback, but that may not work until you can determine the size needed which sounds like "VERY LARGE".
----------------------------------------------------------------------
|
|
|
Re: delete script [message #35705 is a reply to message #35685] |
Mon, 08 October 2001 06:47 |
abdul
Messages: 18 Registered: May 2001
|
Junior Member |
|
|
Thank you for the quick follow up
I am getting the error 01555 snapshot too old(rollback segment too small). this is a one time delete. I have set the transaction size to 16 gig. The commit count is after every 400 deletes. I have been able to delete up to 4mill records on some of the tables.
I am crapping out, when reading from a 15 mill record table and doing a selective delete based on one key field.
There are no delete constraints nor does it appear I need to do a DELETE Cascade. I am executing the script with a shell script and sending the output to nohup.out too view the results.
I am also fetching the record based on the the delete criteria needed, getting the key for that one record then delete the the record and proceede to read the next record in the cursor loop. I have also tried doing a straight delete with the criteria in the where clause of the delete. both encounter the same problems. Any suggestions
Thank you again for the quick response.
----------------------------------------------------------------------
|
|
|