I am trying to resize a varchar2 column, when I get an ORA-14265 error:
ORA-14265: data type or length of a table subpartitioning column may not be changed
With Oracle providing the unhelpful:
// *Cause: User issued ALTER TABLE statement attempting to modify // data type and/or length of a column used to subpartition the // table named in ALTER TABLE statement, which is illegal
// *Action: Avoid modifying data type and/or length of table // subpartitioning column(s)
So I get that Oracle doesn't want me to alter the column. I presume the hash used for the subpartition would be broken by this. How do I get around this though?
The only suggestion I have found so far that seems plausible is to rename the existing table, and recreate the original with updated schema, then select the data from into the recreated table.
I don't particularly like this idea, for reasons of effort and likelihood of human error:
- We have lots of partitions, and behind the scenes I'd have to manually ensure the new table matched up identically, so that supporting maintenance scripts could continue along nicely.
- Then there is disk space. We keep 9 rolling months of data, and I don't have room to suddenly double up on this one table.
Any suggestions would be much appreciated. I hope I'm just missing something easy. Also, if anyone wants to let me know why oracle can't rearrange the data on disk and create a new subpartition hash automagically that would be great.