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 ( , );
