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

Mar 10 2019 10:42 AM by Kishan Mashru

You cannot decrease the length of a column to an extent that any of the data present in the column gets affected. Got the following error when trying to alter the size of a column containing data.

ALTER TABLE <table_name>

MODIFY <column_name> <datatype>;

 

Error report:

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

01441. 00000 -  "cannot decrease column length because some value is too big"

*Cause:    

*Action:

 

DROP TABLE HR.EMP_DETAILS;

CREATE TABLE HR.EMPLOYEE_DETAILS
( EMP_ID NUMBER,
EMP_NAME VARCHAR2(60),
SALARY NUMBER
);

INSERT INTO HR.EMPLOYEE_DETAILS VALUES (1,'Kishan',NULL);
INSERT INTO HR.EMPLOYEE_DETAILS VALUES (2,'Steve', NULL);

COMMIT;

SELECT * FROM HR.EMPLOYEE_DETAILS;

ALTER TABLE HR.EMPLOYEE_DETAILS
MODIFY EMP_NAME VARCHAR2(6);

DESC HR.EMPLOYEE_DETAILS;

Name     Null Type         
-------- ---- ------------ 
EMP_ID        NUMBER       
EMP_NAME      VARCHAR2(6) 
SALARY        NUMBER  

 

-- Trying to alter the size of the column to an extent lower than the size of the data it presently holds.

ALTER TABLE HR.EMPLOYEE_DETAILS
MODIFY EMP_NAME VARCHAR2(4);

 

Error report:
SQL Error: ORA-01441: cannot decrease column length because some value is too big
01441. 00000 -  "cannot decrease column length because some value is too big"
*Cause:    
*Action: