Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
You might be able to use Online Redefinition to do it for you, see docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm – Colin 't Hart Jul 25 '12 at 15:04
Oh, if you manage to use Online Redefinition to solve this problem, could you update us here how you did it? – Colin 't Hart Jul 25 '12 at 17:14
1  
I answered a question on DBMS_REDEFINITION here: dba.stackexchange.com/questions/10101/… - It will require double the space though, so may as well do it manually. – Phil Jul 25 '12 at 22:50

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.