RE: Modify partition range

We had a requirement to keep a running year worth of data. I partitioned the table by month. Every month we truncate that partition and reload it with new data. If you needed to keep 10 years worth of data, I'd say to partition by the last digit of the year. For eight years, perhaps you could convert the year to octal? Just a thought.

-----Original Message-----
From: Lisa_Koivu_at_gelco.com [mailto:Lisa_Koivu_at_gelco.com] Sent: Friday, May 26, 2000 10:36 AM
To: Multiple recipients of list ORACLE-L Subject: Re: Modify partition range

Gregory,

We have time-sensitive partitions in one of our data warehouses (8.0.4) As far
as I know there is no way to easily reset partition ranges. Which means that
every time we approach the PART_MAX partition, I have to take the database down
for an afternoon, split partitions, and rebuild the primary key and all the indexes. Not a trivial thing on a 85M row table.

If you do find out a trick I don't know, I would be interested in what it is.
But after reading through the (scant) doco, it really looked like there was no
way out.

Lisa

Gregory Conron <gconron_at_hfx.andara.com> on 05/25/2000 07:26:17 PM

Please respond to ORACLE-L_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc: (bcc: Lisa Koivu/GELCO)

Have a problem currently with partitioned tables. Quick background - information warehouse database (~400gb) with the average table size around 20 million rows. Many of these tables are partitioned according to a year/month range (e.g. part01 values less than 200013).

The problem is that there is a certain amount of retention of data and any records older than the required retention period can be deleted. I want to use the partitions in a round robin fashion, that is, if I only need 8 years worth of data, I only want to have 8 partitions. When a year can be rolled off, I want to re-use the partition that will now be empty (truncate it then reuse it). However, I cannot find a way to reassign the partition range value without dropping and recreating the partition with the desired range.

Has anybody run up against this problem before, and if so, what was the final solution?

Thanks,
GC

--
There is something inherently wrong with the world when Bill Gates is a
famous billionaire and Dennis Ritchie lives in relative obscurity.
--
Author: Gregory Conron
  INET: gconron_at_hfx.andara.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).






-- 
Author: 
  INET: Lisa_Koivu_at_gelco.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