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)