Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: rollback mechanism is preventing large delete's

Re: rollback mechanism is preventing large delete's

From: <michael_bialik_at_my-deja.com>
Date: Sat, 23 Oct 1999 06:37:33 GMT
Message-ID: <7url3e$66i$1@nnrp1.deja.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US