ORA-01441

This was tested on Oracle 11.2. I created a table with one VARCHAR2 column, which was 15 characters long:

SQL> create table tab1 (col1 varchar2(15))

  2  /

 

Table created.

SQL>

I inserted one row, which was 11 characters in length:

SQL> insert into tab1 values ('Christopher')

  2  /

 

1 row created.

 

SQL> select * from tab1

  2  /

 

COL1

---------------

Christopher

SQL> 

I tried to make the column 10 characters long. This failed, as you might expect, because the row I added earlier had 11 characters: 

SQL> alter table tab1 modify col1 varchar2(10)

  2  /

alter table tab1 modify col1 varchar2(10)

                        *

ERROR at line 1:

ORA-01441: cannot decrease column length because some

value is too big

SQL>

I found the row which was too long, made it a bit shorter then I was able to alter the table successfully. This seems reasonable as VARCHAR2 data is variable length:

SQL> select col1 from tab1 where length(col1) > 10

  2  /

 

COL1

---------------

Christopher

 

SQL> update tab1 set col1 = 'Chris'

  2  where col1 = 'Christopher'

  3  /

 

1 row updated.

 

SQL> alter table tab1 modify col1 varchar2(10)

  2  /

 

Table altered.

 

SQL> select * from tab1

  2  /

 

COL1

----------

Chris

 

SQL>

I did a similar test with a CHAR column. I found that I could not modify it at all unless the column was null. I guess this is because CHAR data is fixed length: 

SQL> create table tab1 (col1 char(15))

  2  /

 

Table created.

 

SQL> insert into tab1 values ('Chris')

  2  /

 

1 row created.

 

SQL> select * from tab1

  2  /

 

COL1

---------------

Chris

 

SQL> alter table tab1 modify col1 char(10)

  2  /

alter table tab1 modify col1 char(10)

                        *

ERROR at line 1:

ORA-01441: cannot decrease column length because some

value is too big

 

SQL> update tab1 set col1 = null

  2  /

 

1 row updated.

 

SQL> alter table tab1 modify col1 char(10)

  2  /

 

Table altered.

 

SQL>