[重慶思莊每日技術分享]-在為表新增了列後執行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>);