Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: rollback mechanism is preventing large delete's
Sorry. I forgot to COMMIT.
What Oracle version you are using?
Oracle 8.x enables partitioning based on key values :
you can create a number of partitions based on date
and DROP the old partition instead of DELETE.
For Oracle 7.x - I don't think it's possible to
"switch off" rollback segments so :
Either you define huge rollback segment and use it for
you overnight processing ( SET ROLLBACK ) or
rewrite you DELETE process :
Instead of
"DELETE FROM tab_name WHERE f_date < ( sysdate - 180 )"
use
DELETE FROM tab_name WHERE f_date < ( sysdate - 180 ) AND
ROWNUM < 5000;
WHILE SQL%ROWCOUNT > 0 LOOP
COMMIT;
DELETE FROM tab_name WHERE f_date < ( sysdate - 180 ) AND
ROWNUM < 5000;
END LOOP;
( Set greatest value that your rollback segment may hold
instead of 5000 ).
HTH. Michael.
In article <7uql04$605$1_at_news.kabelfoon.nl>,
"remco" <rdoremal_at_kabelfoon.nl> wrote:
> Does anybody know how to bypass the rollback mechanism of a server?
>
> We have got a database which is slowly getting full. To prevent the
database
> from getting too full every night we perform (from a client session)
some
> "delete queries" to delete all data which is older than 6 months.
> This clean up process runs every night, however it appears it is not
> succesfull in deleting the data, because too much is deleted at once
and it
> doesn't fit in the rollback datafile anymore (or the maximum number of
> rollback segments is reached).
> What we would like to do is to switch off the rollback for those
delete
> qeuries, since we know that the data may be deleted.
> Another possibility is to swithed off the rollback mechanism of the
server
> for always, since we never use the rollback functionallity. But how?
>
> Regards
>
> remco
>
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Sat Oct 23 1999 - 01:37:33 CDT