I have a really wide table in Oracle that's at type 2 dimension.
Records have from_dates and to_dates with the latest 'current' records having a high end date of 31st Dec 9999. There are currently two partitions on the table, one for the 'current' records and one for 'history' records.
There's a new requirement to only keep the last 12 months of records in the 'history' partition. I interpret this as keeping records that were valid in the last 12 months i.e. where the record's to_date < (this month- 11 months).
Normally if I wanted to get rid of records I'd just drop a partition, but in this case that wouldn't work as I need to retain some of the records in the existing 'history' partition.
Is there any partitioning strategy that could support this or am I barking up the wrong tree?
You aren't accomplishing much with merely two partitions, "current" and "history". You need to repartition this by month. Then you can implement a rolling partition drop of partitions older than 12 months, which will require a bit of scripting.
Normally we use interval partitioning INTERVAL(NUMTOYMINTERVAL(1,'MONTH')) so we don't have to maintain partition adds manually or through scripting. However, unfortunately in your case you won't be able to because of your use of the special date 12/31/9999. This is the maximum date allowable in Oracle. Interval partitioning will internally add the interval to date values when determining whether a new partition is needed or not, and that will overflow the maximum date value allowed and raise an error. The use of this special date essentially disables the use of interval partitioning.
You have no choice but to either change your special "eternity" date to something less than one interval away from 12/31/9999 (anything less than 12/01/9999 would permit monthly interval partitioning, or anything less than 12/31/9998 would permit yearly interval partitioning). Or, as usually happens because code would have to be changed to accommodate these solutions, you have to manually build out partitions ahead of time or create a scheduled script that does it for you.