help on partitioning

I recently made a booboo and made a table with 16 hash partitions instead of 12, now I want to delete these extra 4 partitions but keep the data.

I thought I might be able to just select the data in those partitions out, drop the partitions and then insert the data and the has partitioning would sort itself out - but I can't drop a hashed partition.

So I then thought I would use ctas to create a new table and then drop the old table and rename the new one but I have so many referential constraints, I would have to go disable them temporarily

Does anyone have a better option or am I going to have to use the drop and disable my constrints method.

This is all on 9.2.0.4

Cheers

Dave

You definately have to recreate the table, so you'll have to deal with those pesky constraints etc.

However, 16 is a good number of hash partitions (ie., it's an integer power of two). Why the preference for 12?

well we create the partitions in there own tablespaces - and a tablespace per filesystem (or a factor of 2 per filesystem) - so in this case 12 filesystems so 12 partitions.

we normally do them in powers of 2 - but this customer had to be different (i.e 2 4 8 16 or 32)

That fact that I got 16 partitions means tablespaces 1-4 are twice as full as the other ones, this isnt a problem in itself but I like to keep everything in sync with each other and have them the same size.

The sizings for the tablespaces are also fairly exact so dont want 1-4 to blow up early

Try exchange partitions...

For you windont guys...