ORA-39726 删除压缩表列报错 table compress OLTP vs Basic

2种方法解决这个问题

 

前提是改变表的压缩方式 from Basic Table Compression  to OLTP

首先要知道表是用那种方式压缩，我的case种表是一张分区表，并且还有子分区所以，所以需要查 subpartition 。

select table_name,compression,compress_for,PARTITION_NAME, SUBPARTITION_NAME
  from user_tab_subpartitions
   where table_name = 'TEST_TABLE';

 

返回结果显示的是Basic compression.

change to OLTP compression.

   ALTER TABLE TCDS_FA_FOF_TRANSACTN compress for OLTP;

 

method 1,  set UNUSED COLUMN

ALTER TABLE TEST_TABLE   SET UNUSED COLUMN TEST_NME;

ALTER TABLE TEST_TABLE  DROP UNUSED COLUMNS;

 

method 2, DROP COLUMN

ALTER TABLE TEST_TABLE DROP  COLUMN  TEST_NME;

 

OLTP compression is better than BASIC, when table have duplicated data, compression option can save space. the only difference is BASIC need uncompress action before access data lock, but for OLTP no UNCOMPRESS  option.