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