ORA-12899 value too large error while trying to drop a NUMBER column
I am trying to drop a column from a table in Oracle using a statement like this:
ALTER TABLE “MY_TABLE” DROP COLUMN “ENABLED”;
but I am getting this error:
ORA-12899: value too large for column “MY_TABLE”.”ENABLED” (actual: 184, maximum: 22)
The column is defined as
“ENABLED” NUMBER(1,0) DEFAULT NULL NOT NULL ENABLE,
and it only has values of 0 or 1 in all the rows.
Can anyone tell me why I am getting this error and how to drop the column?
You can try setting it unused first, then dropping unused columns. For more, see Marking Columns Unused.
You maybe have some rows with incorrect encoding, you may want to try a FLIP/FLOP:
-Create a table
NEW_MY_TABLE with the same structure as
MY_TABLE, but without
Insert Into NEW_MY_TABLE (col1,col2...) Select (Col1,Col2...) from MY_TABLE
Rename MY_TABLE to MY_TABLE_OLD
Rename NEW_MY_TABLE to MY_TABLE