Unable to drop default constraint in Oracle

Question

SQL> ALTER TABLE CUSTOMERS MODIFY AGE INT DEFAULT 10;

Table altered.

SQL > ALTER TABLE CUSTOMERS ALTER COLUMN AGE DROP DEFAULT;

ERROR at line 2:
ORA - 01735 : invalid ALTER TABLE OPTION.

Answer 1

Your ALTER statement is wrong and you cannot use two ALTER commands in one statement. And we never drop default value instead we set it to NULL.

If a column has a default value, then you can use the DEFAULT clause to change the default to NULL, but you cannot remove the default value completely. If a column has ever had a default value assigned to it, then the DATA_DEFAULT column of the USER_TAB_COLUMNS data dictionary view will always display either a default value or NULL.

ALTER TABLE

Use the following SQL command to drop the default.

ALTER TABLE constomers MODIFY age DEFAULT NULL;