Help...Rollback Segmenst Grow?

From: Scott Hahn <scotty_at_bestapizza.com>
Date: Mon, 26 Mar 2001 06:49:51 -0800
Message-ID: <F001.002D7BB3.20010326063618@fatcity.com>

Gurus....

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...

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...

Can I issue this statement without growing the rollback segments? (no recover?)
Can I actually shrink the size of the rollback segments after backing up the database?
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). Received on Mon Mar 26 2001 - 08:49:51 CST