ORA-01441: cannot decrease column length because some value is too big

One day when you suddenly found that a field in a table that you have inserted eons ago to Oracle is too large. You wanted varchar2(10), but you accidentally set it to varchar2(100). So with data in it, you wanted to alter the table's structure so that the field is decreased to varchar2(10) only.

To our cleverity, we started to use the alter command. "ALTER table_name MODIFY field_too_long VARCHAR2(10);". But guess what? Oracle will immediately complaint "ORA-01441: cannot decrease column length because some value is too big".

After some finding on the Net, only I realized that not only in Oracle, even DB2 also face the same problem, you can alway increase but not decrease a field length. MSSQL2000 is able to let the user to decrease it field length, as long as the existing data is shorter or the same with the new length.

Actually, from my point of view there are 2 turnarounds for this problem,
1) Either you export everything out, modify the script, remove the original database and import everything in.
2) Or, change the field to an "allow null" field, export the field data out, remove everything in the field to null, alter the table, import all the field data in and set it to "not null" again. "NULL" field does not hold any restriction, if all the values is null, you can even change to other datatype, as long there is no constrain.