Altering CHAR(n BYTE) Column

alter-tableoracle

How to change CHAR (17 byte) to CHAR (16 byte) when there is already a data?

Values are something like this:

012345-000-00001

  • There is a space at the end.

I tried to trim it first before altering the column size but still gets the error due to column is already bigger than 16 byte (due the space at the end).

Thanks !

Best Answer

Modifying an Existing Column Definition

You must set the initialization parameter BLANK_TRIMMING=TRUE to decrease the length of a non-empty CHAR column.

SQL> show parameter BLANK_TRIMMING;

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
blank_trimming               boolean     FALSE


SQL> alter system set BLANK_TRIMMING=true scope=spfile;

System altered.

Then bounce the database.

SQL> startup force;

SQL> update tblchar set col1=TRIM(col1);

SQL> alter table tblchar modify col1 char(16 byte);

Table altered.

Documentation: Altering Tables

Related Question