RE: Help...Rollback Segmenst Grow?

There is no way to stop the use of rollback segments when performing delete operation. Have you considered writing a small PL/SQL procedure to delete the rows? That way, you could commit the work at some interval to control the use of rollback segments. You can certainly shrink them manually as required.  

• Kirti Deshpande Verizon Information Services http://www.superpages.com

> -----Original Message-----
> From: Scott Hahn [SMTP:scotty_at_bestapizza.com]
> Sent: Monday, March 26, 2001 8:36 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Help...Rollback Segmenst Grow?
>
> Gurus....
>
>
> 1)
> I am doing some data movement involving a table creation based on
> another table....
> create table userid.pm_stage_lg_arch tablespace user_data as select
> *from userid.pm_stage_lg where
> entry_timestamp<= to_date('06-feb-01 11:00:00','DD-MON-YY
> HH24:MI:SS');
>
> This creates a table with 1,000,000 rows and grows my tablespace by 360
> MB...
>
> 2)
> I then want to delete the rows from the original table based on the
> same
> date range.....
> delete from nuserid.pm_stage_lg where entry_timestamp <=
> to_date('06-feb-01 11:00:00','DD-MON-YY HH24:MI:SS');
>
> The problem is that when I issue this statement which deletes
> 1,000,000 rows my rollback segments understandably grow by 600 MB...
> 1) Can I issue this statement without growing the rollback
> segments? (no recover?)
> 2) Can I actually shrink the size of the rollback
> segments
> after backing up the database?
>
> 3) As you see in the statement I am basing my select on the date which I
> need to be exact...Is there a way I could do it as select sysdate-45 from
> dual and have it returned in teh same format as 'DD-MON-YY HH24:MI:SS' ???
> I have been trying this for a while and cant get it..
>
> Thanks for the help. This is a DB that is limited in Hard drive space so
> I
> need to conserve..
>
> Scott Hahn
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Scott Hahn
> INET: scotty_at_bestapizza.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: kirti.deshpande_at_verizon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).