Home » SQL & PL/SQL » SQL & PL/SQL » delete script
delete script [message #35685] Fri, 05 October 2001 08:38 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.

----------------------------------------------------------------------
Previous Topic: File in the Client Side
Next Topic: Compilation error
Goto Forum:
  


Current Time: Sat May 25 09:36:27 CDT 2024