Oracle 表开启压缩属性的情况下无法删除unused列

 

Oracle 表开启压缩属性的情况下无法删除unused列

 

具体如下，直接删除可以成功执行但是实际没有成功删除：

15:07:54 SYS@xxxxxx2(11)> select COLUMN_NAME,HIDDEN_COLUMN from dba_tab_cols where owner='TEST' AND TABLE_NAME='HIDDEN_TABLE' and HIDDEN_COLUMN='YES';;

COLUMN_NAME                                                                                HIDDEN_CO
------------------------------------------------------------------------------------------ ---------
SYS_C00044_21091516:47:36$                                                                 YES

Elapsed: 00:00:00.01

15:01:12 SYS@xxxxxx2(11)> alter table TEST.HIDDEN_TABLE parallel 32;

Table altered.

Elapsed: 00:00:00.02
15:01:43 SYS@xxxxxx2(11)> alter table TEST.HIDDEN_TABLE drop unused columns;

Table altered.

Elapsed: 00:00:00.06

15:03:35 SYS@xxxxxx2(11)> select COLUMN_NAME,HIDDEN_COLUMN from dba_tab_cols where owner='TEST' AND TABLE_NAME='HIDDEN_TABLE' and HIDDEN_COLUMN='YES';;

COLUMN_NAME                                                                                HIDDEN_CO
------------------------------------------------------------------------------------------ ---------
SYS_C00044_21091516:47:36$                                                                 YES

Elapsed: 00:00:00.01

 

取消表的压缩属性后可以成功执行：

15:10:55 SYS@xxxxxx2(11)> alter table TEST.HIDDEN_TABLE nocompress;

Table altered.

Elapsed: 00:00:00.19

15:08:31 SYS@xxxxxx2(11)> alter table TEST.HIDDEN_TABLE drop unused columns;

Table altered.

Elapsed: 00:11:07.25

15:24:42 SYS@xxxxxx2(11)> select COLUMN_NAME,HIDDEN_COLUMN from dba_tab_cols where owner='TEST' AND TABLE_NAME='HIDDEN_TABLE' and HIDDEN_COLUMN='YES';

no rows selected

Elapsed: 00:00:00.03
15:25:32 SYS@xxxxxx2(11)> alter table TEST.HIDDEN_TABLE noparallel;

Table altered.

Elapsed: 00:00:00.03
15:25:36 SYS@xxxxxx2(11)> alter table TEST.HIDDEN_TABLE compress for oltp;

Table altered.

Elapsed: 00:00:00.14

 

 

参考：Dropping Unused Columns fails if compression is enabled (文档 ID 2171802.1)