-
Rollback segment problem
Hi everybody,
Facing an age old problem
I am running a delete statement in a cursor (to commit in between)
I am commiting every 1000 records. The pl/sql block would delete about 8 million records.My undo management is automatic
Some stats:
table size : 1.2 GB
undo tablespace size 1 GB
Records to be deleted : about 8 million
commit : every 1000 records
undo retention: 900
Still it gives the error
ORA-01555: snapshot too old: rollback segment number 11 with name "_SYSSMU11$"
I have a hunch that my undo retention has to be increased.
If i am correct then about how much should i increase
Any help will be appreciated
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
are you fetching across a commit - cos that would be naughty
post the code
-
Declare
Cursor del_row is select rowid from temp_tab a where exists
(select 1 from temp_tab b where ((a.amount>0
and b.AMOUNT<0) or(a.amount<0 and b.AMOUNT>0))
and abs(a.amount)=abs(b.AMOUNT)
and a.md=b.MD
and a.comment=b.COMMENT
and a.type=b.TYPE);
ctr number:=0;
Begin
for one_row in del_row loop
delete temp_tab where rowid=one_row.rowid;
ctr:=ctr+1;
if ctr=1000 then
commit;
dbms_output.put_line('1000 rows commited');
ctr:=0;
end if;
end loop;
exception
when others then
dbms_output.put_line(sqlerrm);
end;
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
PS: the query is doing an index range scan for the sub query and FFS for the parent.
I am forcing parallel DML also,i.e.,alter session force parallel DML.
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
So you really want to use a collection to store the primary key of all the records you want to delete.
-
Tarry Singh
I'm a JOLE(JavaOracleLinuxEnthusiast)
--- Everything was meant to be---
-
well you are fetching across a commit - that is a such a bad thing to do and can guarantee yourself a ORA-1555 which you have got
-
http://asktom.oracle.com/pls/ask/f?p...D:546822742166
so had you set your undo to 10G maybe you'd have escaped the error.
Tarry Singh
I'm a JOLE(JavaOracleLinuxEnthusiast)
--- Everything was meant to be---
-
Originally posted by davey23uk
well you are fetching across a commit - that is a such a bad thing to do and can guarantee yourself a ORA-1555 which you have got
I ran the delete without the commit also :
Delete from temp_tab a where exists
(select 1 from temp_tab b where ((a.amount>0
and b.AMOUNT<0) or(a.amount<0 and b.AMOUNT>0))
and abs(a.amount)=abs(b.AMOUNT)
and a.md=b.MD
and a.comment=b.COMMENT
and a.type=b.TYPE);
Got the same error
Latest update is :
Set undo retention to 5000
commit every 5000 rows instead of 1000
Any solutions??
Am really in a fix with this
Last edited by simply_dba; 04-28-2005 at 12:30 PM.
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
Oracle DBA
-
Originally posted by simply_dba
I ran the delete without the commit also :
Got the same error
Latest update is :
Set undo retention to 5000
commit every 5000 rows instead of 1000
Any solutions??
Am really in a fix with this
Load the data into a collection, then you can either do a bulk bind delete with only one commit or you can iterate through the collection and do as many commits as you like. Once you load the data into a collection there is no problem maintaining a read consistant view of the table which is what usually causes the ORA-1555 errors.
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
|