在为表添加了列后执行ALTER TABLE SHRINK SPACE 提示ORA-8102

刘泽宇

APPLIES TO:Oracle Database - Standard Edition - Version 12.1.0.1 to 19.7.0.0.0 [Release 12.1 to 19]
Information in this document applies to any platform.
SYMPTOMS

ALTER TABLE SHRINK SPACE command returns ORA-8102 after adding column.

SQL> alter table <TABLE_NAME> shrink space;
alter table <TABLE_NAME> shrink space
*
ERROR at line 1:
ORA-08102: index key not found, obj# NNNNN, file N, block NNNNN (2)

CHANGES

After adding a column with add column optimization and creating a new index on the column.
"add column optimization" is enabled by default when adding a column with DEFAULT value.

alter table <TABLE_NAME> add <COLUMN_NAME_NEW> number default 10 not null;
create index <INDEX_NAME_NEW> on <TABLE_NAME> ( <COLUMN_NAME1>, <COLUMN_NAME_NEW>);
CAUSE

This problem has been investigated under Bug:22473983.

SOLUTION

After the error:
Drop a index on the table for the added column. And rerun ALTER TABLE SHRINK SPACE command.
After completion the shrink table, recreate the index as you need.

drop index <INDEX_NAME_NEW>;

Before the adding columns:
Before adding columns, set parameter "_add_col_optim_enabled" = false.

alter session set "_add_col_optim_enabled" = false;
alter table <TABLE_NAME> add <COLUMN_NAME_NEW> number default 10;
create index <INDEX_NAME_NEW> on <TABLE_NAME> ( <COLUMN_NAME1>, <COLUMN_NAME_NEW>);