2

I'm trying to remove column from DB and got a strange error:

Error starting at line 147 in command: alter table config drop column dte Error report: SQL Error: ORA-12899: value too large for column "OWNER"."CONFIG"."DTE" (actual: 3, maximum: 1) 12899. 00000 - "value too large for column %s (actual: %s, maximum: %s)"

*Cause: An attempt was made to insert or update a column with a value which is too wide for the width of the destination column. The name of the column is given, along with the actual width of the value, and the maximum allowed width of the column. Note that widths are reported in characters if character length semantics are in effect for the column, otherwise widths are reported in bytes.

*Action: Examine the SQL statement for correctness. Check source and destination column data types. Either make the destination column wider, or use a subset of the source column (i.e. use substring).

I don't have triggers on this table. Could anyone advice on it? Thanks!

UPD: we were able to delete it using alter table ... set unused ... and alter table drop unused columns after it. But still have no idea why simple drop doesn't work.

5
  • The only constraint is not null for this column
    – qqq123
    Nov 6, 2014 at 14:39
  • Is it any chance you have ddl triggers on database or schema level ?
    – a1ex07
    Nov 6, 2014 at 17:32
  • @a1ex07, there are some db-level triggers, but they are not related to this table.
    – qqq123
    Nov 7, 2014 at 7:21
  • Have you tried to grow the DTE colum to VARCHAR2(5)? You might get a different error when you do that, which migh help you to understand why you can't drop the table.
    – Gandolf989
    Nov 10, 2014 at 18:19
  • Care to show the original table DDL?
    – mustaccio
    Mar 20, 2015 at 19:45

1 Answer 1

-2

You can do it by increasing column size, then drop . (Because drop column statement try to validate the data, then drop ) like ..

SQL> ALTER TABLE OWNER.CONFIG
     MODIFY(DTE VARCHAR2(500 BYTE));

SQL> ALTER TABLE OWNER.CONFIG DROP COLUMN DTE ;

example:-

SQL> select * from v$version ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production


SQL> ALTER TABLE ORCL.FORM DROP COLUMN CURRENT_STEP;
ALTER TABLE ORCL.FORM DROP COLUMN CURRENT_STEP
*

ERROR at line 1:
ORA-12899: value too large for column "ORCL"."FORM"."CURRENT_STEP" (actual:
6, maximum: 2)


SQL> ALTER TABLE ORCL.FORM
  2  MODIFY(CURRENT_STEP VARCHAR2(500 BYTE));


Table altered.


SQL> ALTER TABLE ORCL.FORM DROP COLUMN CURRENT_STEP;


Table altered.
2
  • 1
    Did you just copy/paste?
    – Racer SQL
    Mar 20, 2015 at 19:17
  • what do you mean?
    – Halim
    Mar 20, 2015 at 19:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.