My Oracle Support Banner

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

Last updated on AUGUST 23, 2023

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


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