PROBLEM:

While decreasing length of a column using alter statement, got below error.

SQL> alter table dbatab modify ( dbaname varchar2(5));
alter table dbatab modify ( dbaname varchar2(5))
*
ERROR at line 1:
ORA-01441: cannot decrease column length because some value is too big

SOLUTION:

Attempt to reduce length of a column which contains data, whose length is more than new column length , will result in error.

I.e if we are trying to modifying the column to varchar2(5), And that column contains data ,whose length is more than 5, then it will throw error.

So the only solution is to find the row-column values whose length is more that 5 and modify the data to a small value.

Find the rows which has length >= 5;

SQL> select DBANAME from DBATAB;

DBANAME
------------------------------
RAVIKUMAR
RAju
SANKAR


SQL> select dbaname from dbatab where LENGTH(DBANAME) >5;

DBANAME
------------------------------
RAVIKUMAR
SANKAR

We have two values for which column length is > 5. Now either trim the value to length 5 or set them to NULL.

SQL> update dbatab set DBANAME=substr(DBANAME,1,5) where LENGTH(DBANAME) > 5;

2 rows updated.

SQL> select DBANAME from DBATAB;

DBANAME
------------------------------
RAVIK
RAju
SANKA

SQL> commit;

Commit complete.

Now run the alter statement:

SQL> alter table dbatab modify ( dbaname varchar2(5));

Table altered.