470,609 Members | 1,819 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,609 developers. It's quick & easy.

Problem with Delete

I am facing a problem with DELETE statement. I have to delete around 2 billion records from a table with 150 columns. on execution of the delete statement, it fails after some time with message " Log Space full".

I am having a log space og 2 GB. But I guess thats proving in sufficient. I am using one indexed column to delete the data. If I use other column as filter statement I have to run the query more than 1000 times.

Is there any way to overcome this?
Dec 11 '07 #1
2 1269
297 Expert 100+

if you want to delete all rows from that table, a "LOAD (client) from empty.file of del replace into my.table" is the fastest way to do.

If the number of rows to be deleted is far more that those to be preserved, build a temp. table, declare a cursor on your original table and SELECT the rows you want to keep. Then LOAD these from the cursor into the temp. table, and afterwards reverse the LOAD (with replace option).

If you have to stick to a DELETE statement, there's no other way than to increase the log space and/or do it in several steps, e.g. if you got a date column, set a limit on monthly base. You could also choose to aprox. cut in half the amount of rows to be deleted, time and time again, as long as it takes to run the DELETE successfully. That way you'll learn how many rows/data of that table you may delete with 2GB of log space.

Regards, Bernd
Dec 16 '07 #2

If you are a DBA then you just update CFG parameter logsecond -1 then executive the command after completion of that u can reset this CFG
Dec 19 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by michael | last post: by
6 posts views Thread by Matan Nassau | last post: by
13 posts views Thread by Bob Darlington | last post: by
14 posts views Thread by placid | last post: by
6 posts views Thread by Lighter | last post: by
1 post views Thread by Rebecca Tsukalas | last post: by
2 posts views Thread by Martin | last post: by
2 posts views Thread by dean.cochrane | last post: by
11 posts views Thread by Ed Dror | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.