在压缩表上删除字段的处理方法

1.普通表(未分区)

SQL> Alter Table tb_hxl_id Compress;

Table altered.

SQL> Alter Table tb_hxl_id Move Compress;

Table altered.

SQL> Alter Table tb_hxl_id Add c Varchar2(2);

Table altered.

SQL> Alter Table tb_hxl_id Drop Column c;
Alter Table tb_hxl_id Drop Column c
                                  *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables


SQL> Alter Table tb_hxl_id Move Nocompress; -- 需要进行解压缩后才能删除字段


Table altered.

SQL> Alter Table tb_hxl_id Drop Column c;

Table altered.

 

2.分区表

 

SQL> Select aa.table_name,aa.partitioning_type
  2 From dba_part_tables aa
  3 Where aa.table_name = 'TB_HXL_LIST';

TABLE_NAME PARTITION
------------------------------ ---------

TB_HXL_LIST LIST

SQL> Select
  2 aa.compression,
  3 aa.partition_name
  4 From dba_tab_partitions aa
  5 Where aa.table_name = 'TB_HXL_LIST';

COMPRESS PARTITION_NAME
-------- ------------------------------

ENABLED P_L1
ENABLED P_L2
ENABLED P_L3
ENABLED P_L4

SQL> Alter Table tb_hxl_list compress;

Table altered.

SQL> Alter Table TB_HXL_LIST
  2 Move Partition P_L1 compress;

Table altered.

SQL> Alter Table TB_HXL_LIST Add b Varchar2(2);

Table altered.

SQL> Alter Table TB_HXL_LIST Drop Column b;
Alter Table TB_HXL_LIST Drop Column b
                                    *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables


SQL> Alter Table TB_HXL_LIST
  2 Move Partition P_L1 Nocompress;

Table altered.

SQL> Alter Table TB_HXL_LIST Drop Column b;
Alter Table TB_HXL_LIST Drop Column b
                                    *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

SQL> alter table TB_HXL_LIST set unused column b; -- 压缩的分区表不能删除字段,只能设置unused


Table altered.

ORA-39726: unsupported add/drop column operation on compressed tables
Cause: An unsupported add/drop column operation for compressed table was attemped.
 Action: When adding a column, do not specify a default value. DROP column is only supported in the form of SET UNUSED column (meta-data drop column).