Unable To Add A New LOB Column with DEFAULT VALUE (Doc ID 2902413.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

A blob column is being added with default value encounters the error below:

ORA-39726: unsupported add/drop column operation on compressed tables

ALTER TABLE MYTABLE ADD EDIMAGE BLOB DEFAULT EMPTY_BLOB();

SQL> ALTER TABLE MYTABLE ADD EDIMAGE BLOB DEFAULT EMPTY_BLOB();
ALTER TABLE CONFIGURATION_ITEM_TEST ADD EDIMAGE BLOB DEFAULT EMPTY_BLOB()
  *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables


SQL> ALTER TABLE MYTABLE ADD EDIMAGE varchar2(20) DEFAULT 'SUBJECT';

Table altered.


This works if the added column does NOT have a default value.

example

SQL> ALTER TABLE IDMUSER.CONFIGURATION_ITEM_TEST ADD EDIMAGE1 BLOB;

Table altered.

SQL> ALTER TABLE IDMUSER.CONFIGURATION_ITEM_TEST modify EDIMAGE1 DEFAULT EMPTY_BLOB();

Table altered.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.