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.
Best Answer
The solution is
dbms_redefinition
package. Basically redefition will move, online, to a new table (called interim table), then move all dependent objets like indexes and last exchange the interim table with the original one.What you have to do is:
Use
dbms_redefinition
in the following way:If you run redefinition with SYSTEM user there is no problem. If you want to execute it with a less privileged user you have to trick some privileges in order to get it working. Privileges required are:
Tables with the following characteristics cannot be redefined online:
Other restrictions: