如何在压缩表中删除列?

压缩表后,无法再删除列:

CREATE TABLE foo (p NUMBER, i NUMBER, j NUMBER) COMPRESS BASIC;

ALTER TABLE foo DROP COLUMN j;
ORA-39726: unsupported add/drop column operation on compressed tables

如果使用提前压缩,则有可能:

CREATE TABLE foo (p NUMBER, i NUMBER, j NUMBER) COMPRESS FOR OLTP;

ALTER TABLE foo DROP COLUMN j;
Table FOO altered.

但是,该列并没有真正删除,只是隐藏了:

SELECT column_name, data_type, hidden_column 
  FROM user_tab_cols WHERE table_name = 'FOO';

COLUMN_NAME                 DATA_TYPE  HIDDEN_COLUMN
P                           NUMBER     NO
I                           NUMBER     NO
SYS_C00002_18030204:09:26$  NUMBER     YES

这会在交换分区时导致错误:

CREATE TABLE par (p NUMBER, i NUMBER) 
 PARTITION BY LIST(p)(
 PARTITION p1 VALUES(1), 
 PARTITION p2 VALUES(2)
);

ALTER TABLE par EXCHANGE PARTITION p1 WITH TABLE foo;
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

如何删除隐藏列?我试过了

ALTER TABLE foo DROP UNUSED COLUMNS;

但这无助于:

SELECT column_name, data_type, hidden_column 
  FROM user_tab_cols WHERE table_name = 'BAR';

COLUMN_NAME                 DATA_TYPE  HIDDEN_COLUMN
P                           NUMBER     NO
I                           NUMBER     NO
SYS_C00002_18030204:09:26$  NUMBER     YES