Table Partiotioning of Oracle 8

Hi guys,

I have a problem here, any help will greatly be appreciated.

We have database not so large, but my manager has hooked to the idea of partitioning the tables. Our partitioning has to be on dates. I referred to Oracle manual, some white papers, and news group, everyone talks and praises such temporal partitioning that can be achieved in Oracle. But they all say the same, create a new partition for every one month, three months or whatever, and drop the oldest. Unfortunately, in my case I can not drop the oldest, it needs to be there as long the users want it. If I create a partition every month, I will end up with hundreds of partition. The only feasible solution is have different partitions for the last three months and have one partition for the rest (earlier months) . (The partition that has old data will be huge compared to others, which is all right for us as the older data is hardly accessed, and most of the queries are on date.) This I can achieve by creating a new partition every month and merge the three months old partition to the one that holds the older data. I looked up the manual, there is split, drop, truncate and modify, but there is no merge. OK, I will (do it myself) drop the third month partition, after backing the data up, and load it into the older data partition: this is not possible because the third month data will violate the partition definition of the older data partition, and I have not come across any syntax in the manual that allows changing the partition definition. The sure way, of course, is backup the table, drop, and recreate with new partition definition, and load the data back, but this has a hitch, we have quite a few tables with few million rows and repartitioning them in the allowed down time may not be possible. This is my problem, how do I achieve such partitioning without the nightmare of recreating the entire table.

If anyone can help me in this, I will greatly appreciate it.

Thank you very much. Received on Thu Dec 24 1998 - 23:34:37 CST