-
Hi,
I am trying to delete about a million rows from a table using a pl/sql block but am getting an error ORA-01002: fetch out of sequence
Here is the sample of the block
1 declare
2 cursor selectfordelete is select * from test where region not in ('REGROLL','WWF','WWR','CON','
3 'CAN','LATAM','OWH','REGEAST','AFRME','ASPAC','EUR','OEH','OTH','NCI','WOUS','WOUSCON') for upd
4 begin
5 for i in selectfordelete loop
6 delete from test where current of selectfordelete;
7 commit;
8 end loop;
9* end;
10 /
declare
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 5
Please suuggest something.
Thanks
Anurag
-
Remove the COMMIT; command from line 7.
That should help you.
Sam
Thanx
Sam
Life is a journey, not a destination!
-
Hi Sam,
The reason I have a commit there is becuase the cursor fetches mote than a million records and deleteing them without committing will run out of rollback space.
The only reason I am using a cursor here is to do commits otherwise I could have used a simple delete command.
Is there any other way i can delete a million rows.
Thanks
Anurag
-
This one I think you can use the
SET AUTOCOMMIT #
Get it autocommitted after certain number of operations. That way I hope that you could solve this problem on doing the execution in SQL.
Sam
Thanx
Sam
Life is a journey, not a destination!
-
Sorry, but it seems to me you should OPEN cursor first
and don't forget to CLOSE it at the end.
-
The cursor FOR loop would implicitly open the cursor and close the cursor.
Sam
Thanx
Sam
Life is a journey, not a destination!
-
Anurag,
The way you are doing this is totaly wrong. I can't say why you are getting this "Fetch out of sequence" error, but even if you didn't get this one you will most certanly get another nasty one: ORA-1555: Snapshot to old...
What you are doing is called "fetch accros commit" - you open a cursor, feTch some record from it, perform some changes to the table you are fetching from, commit the changes and continue fetching from this same cursor without closing it and opening it again. The perfect scenario for ORA-1555. The more often you commit, the sooner you'll get ORA-1555.
The most simple way for your example would be to commit every N records (say N=100,000), but to open a new cursor (implicit or explicit one) after each commit:
LOOP
DELETE FROM test WHERE rownum <= 100000 AND ....;
EXIT WHEN SQL%ROWCOUNT = 0;
END;
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
It still Does not work
Hi Jurij,
I tried your solution and it also does not work.
Below is the sample of the code. It is not able to delete even 50 records at a time and gives a ORA-01562: failed to extend rollback segment number 3. I tried increasing the roll back segment size but I think that wont help as it is not commiting and trying to delete all the 1 million records.
Please point out what am i doing wrong here.
Thanks
Anurag
SQL> ed
Wrote file afiedt.buf
1 declare
2 cnt number := 0;
3 begin
4 select count(*) into cnt from test;
5 cnt := cnt/10000;
6 cnt := ceil(cnt);
7 for i in 1..cnt loop
8 loop
9 delete from test where rownum <=50 and region not in ('REGROLL','WWF','WWR','CON','REGWEST','
10 'CAN','LATAM','OWH','REGEAST','AFRME','ASPAC','EUR','OEH','OTH','NCI','WOUS','WOUSCON');
11 EXIT WHEN SQL%ROWCOUNT = 0;
12 end loop;
13 commit;
14 end loop;
15* end;
SQL> /
declare
*
ERROR at line 1:
ORA-01562: failed to extend rollback segment number 3
ORA-01650: unable to extend rollback segment RBS2 by 64 in tablespace RBS
ORA-06512: at line 9
[Edited by anuragmin on 06-08-2001 at 03:20 PM]
-
how about puting commit after EXIT WHEN SQL%ROWCOUNT = 0; inside the inner loop?
-
Nope,
I tried putting the commit inside the loop after the exit when command but still it is giving me the error
declare
cnt number := 0;
begin
select count(*) into cnt from test;
cnt := cnt/10000;
cnt := ceil(cnt);
for i in 1..cnt loop
loop
delete from test where rownum <=50 and region not in ('REGROLL','WWF','WWR','CON','REGWEST','
'CAN','LATAM','OWH','REGEAST','AFRME','ASPAC','EUR','OEH','OTH','NCI','WOUS','WOUSCON');
EXIT WHEN SQL%ROWCOUNT = 0;
commit;
end loop;
end loop;
end;
/
declare
*
ERROR at line 1:
ORA-01562: failed to extend rollback segment number 3
ORA-01562: failed to extend rollback segment number 3
ORA-06512: at line 9
Anurag
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|