Oracle COMPRESS BASIC表删列行为不一致及ORA-39726预判问题

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 31 08:37:58 2023
Version 19.14.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Connected.

SQL>column banner_full format A71
SQL>select banner_full from v$version
  2  /
BANNER_FULL
-----------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0

技术问题 #

• 新建的COMPRESS BASIC表添加列后，删除该列时未触发ORA-39726错误（提示"不支持对压缩表执行添加/删除列操作"），而是被静默转换为SET UNUSED COLUMN操作。但19c官方文档仍明确说明："对于使用COMPRESS BASIC的表，可将列设为UNUSED，但无法删除列"，这与现有行为不符。
• 如何提前判断一个COMPRESS BASIC表删除列时是否会触发ORA-39726错误？
• 对于在数据库为12c时创建的旧COMPRESS BASIC表，在19c中删除列会触发ORA-39726错误，但仅执行alter table t_old nocompress修改数据字典后，即可直接删除列（并非转为UNUSED），无需执行MOVE NOCOMPRESS操作，这与之前版本要求不同，原因是什么？

新建表操作示例 #

SQL>create table t_new compress as select * from all_users
  2  /
Table created.

SQL>alter table t_new add x number
  2  /
Table altered.

SQL>alter table t_new drop column x
  2  /
Table altered.

SQL>column table_name format A10
SQL>select * from user_unused_col_tabs
  2  /
TABLE_NAME      COUNT
---------- ----------
T_NEW               1

SQL>alter table t_new nocompress
  2  /
Table altered.

SQL>alter table t_new drop unused columns
  2  /
Table altered.

SQL>select * from user_unused_col_tabs
  2  /
TABLE_NAME      COUNT
---------- ----------
T_NEW               1

SQL>alter table t_new move nocompress
  2  /
Table altered.

SQL>alter table t_new drop unused columns
  2  /
Table altered.

SQL>select * from user_unused_col_tabs
  2  /
no rows selected

SQL>alter table t_new compress
  2  /
Table altered.

SQL>drop table t_new
  2  /
Table dropped.

SQL>

旧表操作示例 #

-- 在上述同一19c环境中，使用数据库为12c时创建的表
SQL>alter table t_old add x number
  2  /
Table altered.

SQL>update t_old set x = 1
  2  /
3845 rows updated.

SQL>alter table t_old drop column x
  2  /
alter table t_old drop column x
                                        *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

SQL>alter table t_old nocompress
  2  /
Table altered.

SQL>alter table t_old drop column x
  2  /
Table altered.

SQL>select * from user_unused_col_tabs
  2  /
no rows selected

SQL>alter table t_old compress
  2  /
Table altered.

SQL>

问题解答 #

问题1：新建COMPRESS BASIC表删列静默转为UNUSED的原因 #

这是Oracle 19c针对COMPRESS BASIC表的行为变更，官方文档可能未及时更新。19c中对新建的COMPRESS BASIC表执行DROP COLUMN时，会自动降级为SET UNUSED COLUMN操作，避免直接抛出ORA-39726错误，属于兼容性优化。但这种转换仅对19c环境下新建的COMPRESS BASIC表生效，旧版本迁移过来的表不适用。

问题2：提前判断删列是否触发ORA-39726的方法 #

核心区分点在于表的创建环境版本：19c原生创建的COMPRESS BASIC表会自动转为UNUSED，旧版本迁移的表则会抛出错误。可通过以下方式验证：

1. 查询表的创建时间与数据库版本匹配度：

SELECT created FROM user_tables WHERE table_name = '你的表名';

2. 查看表的压缩属性细节：

SELECT property_name, property_value 
FROM user_tab_properties 
WHERE table_name = '你的表名' 
AND property_name = 'COMPRESS_FOR';

若表是19c新建的COMPRESS BASIC表，执行删列不会触发错误；从12c迁移的表则大概率触发ORA-39726。

问题3：旧COMPRESS BASIC表执行NOCOMPRESS后可直接删列的原因 #

这是19c对ALTER TABLE NOCOMPRESS操作的优化：在12c及以前版本，NOCOMPRESS仅修改数据字典标记，实际数据仍保持压缩格式，因此删列仍受限制；但19c中，对从旧版本迁移的COMPRESS BASIC表执行NOCOMPRESS时，会自动触发元数据层面的兼容性调整，使得表的存储格式不再受COMPRESS BASIC的删列限制，因此无需执行MOVE NOCOMPRESS即可直接删列。这种优化目的是降低版本升级后的操作复杂度。

内容的提问来源于stack exchange，提问作者Alex Bartsmon