That's what we are doing. No maxvalue partition exists in our datamart
tables partitioned on date ranges. These dates are verified at source. Old
partitions will be dropped and space resused for new ones that will get
added as time goes on. Keeping a set max number of partitions for 10 years
worth of data. Using just local indexes and only adding new partitions (not
spliting or merging) will keep partition maintenance to the minimum and
straightforward.
- Kirti
> -----Original Message-----
> From: Nancy McCormick [SMTP:nancy.mccormick_at_sbti.com]
> Sent: Friday, May 26, 2000 11:24 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Modify partition range
>
> I haven't done this but I was told to not use the "maxvalue" for the
> range definitions. For date range definition I was told to set up the
> initial partition ranges for the expected dates. Then as you roll to a
> new date range you alter the table and add a new partition for the new
> dates and backup/drop the partition you no longer need. I know this
> doesn't help with reusing existing partitions but it seems you could
> automate the add new partition/drop old partition.
>
> Does this make any sense?
> Nancy
>
> Lisa_Koivu_at_gelco.com wrote:
> >
> > 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
> > also send the HELP command for other information (like subscribing).
> --
> Author: Nancy McCormick
> INET: nancy.mccormick_at_sbti.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
Received on Fri May 26 2000 - 12:20:52 CDT
