Skip to Main Content

SQLcl

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

ORA-39726 error when column dropped via Liquibase

vmuthadiDec 29 2023 — edited Dec 29 2023

Hi,

I am trying to setup CICD pipeline for database objects and using liquibase commands of sqlcl for an entire schema. We have compression enabled at database level and all tables created have compress enabled by default as expected.

As part of CICD, we generated entire schema DDL using below

lb generate-schema -split

Then we are trying to import the entire db objects to target database using below command

lb update -changelog-file database/controller.xml

Everything is working expected like if a table/columns are missing, got created. But when a column needs to dropped as its not available in source database, getting below error.

Running Changeset: table/oms_master_table.xml::6bbb5aabaedaf42c1fdaac3549600adcd9efc0fc::(OMSHR)-Generated Error starting at line : 1 in command -
  ALTER TABLE "OMS_MASTER" DROP ("OPERATING_SYSTEM")
  Error report -
 ORA-39726: unsupported add/drop column operation on compressed tables 
 39726. 00000 -  "unsupported add/drop column operation on compressed tables" 
*Cause:    An unsupported add/drop column operation for compressed table
            was attemped.  
*Action:   When adding a column, do not specify a default value.
             DROP column is only supported in the form of SET UNUSED column
            (meta-data drop column). 
Rolling back changeset. 
Rollback completed successfully.

I understand that for a compressed table, before we drop a column we need to uncompress and drop. Somehow liquibase is not handling this scenario automatically.

Can you please advise if there is any option during import, that makes liquibase to uncompress and drop and the compress automatically.

Comments
Post Details
Added on Dec 29 2023
0 comments
59 views