[chuck_forbes]

So, I've just been getting my feet wet in the documentation, and I already have a question:

We have a table with YEAR and QTR separated into their own columns. I'd like to use these as a basis for partitioning, but it looks like there'll be a maintenance overhead of going in, perhaps annually, and altering the table to include 4 new partitions for every year?

Am I on the right track? It'd be much easier if there was an automated way of declaring that a partition be created everytime a unique set of values were introduced into the system. That's what I'm hoping for, I guess.

v. 10.1.0.4
---=Chuck

[anacedent]

manual maintenance is required & if/when you forget it is a real pain to clean up the mess.

You need to read up on HIGH VALUES.

[chuck_forbes]

Is is still a pain if you have ENABLE ROW MOVEMENT in place, alongside a MAXVALUE partition? I was hoping that in the case I forgot to create a partition, and rows were inserted into the MAXVALUE partition, that an ALTER TABLE which added a new partition would automatically move the appropriate MAXVALUE rows.

[anacedent]

Think about the following restrictions.
1) Partitions can only be added at the "top".
2) A new partition can not be added "between" two existing partition.
3) No value is greater than HIGH VALUES; which means a partition can not be created on "top" (after) the HIGH VALUES partition.

IIRC, Oracle won't allow a partition to be dropped if it contains data.

So when you forget here is what needs to occur.
1) Move all the data out of the HIGH VALUES partition.
2) Drop HIGH VALUES partition
3) create new partitions
4) create HIGH VALUES partition
5) move data into appropriate new partition

All of which can be disruptive for 24x7 application

[beilstwh]

I don't know what version you are using, but Oracle 11G now has automatic partition generation. Maybe you should look at upgrading if you want this option.

[AlanP]

Alternatively why not just create your future partitions now if you have the disk space. We tend to create our partitions going upto 5 years in advance.

Alternatively the partition maintenance isnt really that big a job unless you have loads of partitioned tables in which case you could create a procedure to add the new partitions and call it from an oracle job near the end of the year.

Alan

[chuck_forbes]

That's a good point. We're not really poised to endure the testing necessary to go to 11g at this time.

It still sounds like a yearly job, in itself, wouldn't be sufficient if I have a category which includes the MAXVALUE keyword, though. At least things wouldn't be easy. I'd have to add some code which moved all data out of that top partition, dropped that partition, added some new partitions at the top (including a new MAXVALUE partition), and then moved the data back into the table.

Does that sound like I'm on the right track?
--=cf

[anacedent]

If you are proactive, then the HIGH VALUES partition will never contain any data.

[chuck_forbes]

OK, I see what you're saying. So in that case, dropping the HIGH VALUES partition, adding new partitions at the top, then re-adding the HIGH VALUES partition, requires 0 data movement.

[anacedent]

Originally Posted by chuck_forbes

OK, I see what you're saying. So in that case, dropping the HIGH VALUES partition, adding new partitions at the top, then re-adding the HIGH VALUES partition, requires 0 data movement.

That is 100% correct; assuming it is done PROACTIVELY

This operation can be automated to run as a batch job once a year;
or it can be done manually during normal working hours.
Nothing in the application will be referencing HIGH VALUES or the new partition(s).