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 shrink space;

alter table 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 add number default 10 not null;

create index on ( , );

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 add number default 10;

create index on ( , );

ITPub博客

[重庆思庄每日技术分享]-在为表添加了列后执行ALTER TABLE SHRINK SPACE 提示ORA-8102