Friday, June 19, 2015

Few ORA- errors during "alter index ... rebuild"

[oracle@vmxdb01 ~]$ grep ORA- move_index.log |sort|uniq
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_WRI$_OPTSTAT_IND_OBJ#_ST' or partition of such index is
ORA-02327: cannot create index on expression with datatype ADT
ORA-02327: cannot create index on expression with datatype LOB
ORA-02327: cannot create index on expression with datatype NAMED ARRAY TYPE
ORA-28650: Primary index on an IOT cannot be rebuilt

ORA-00604 & ORA-01502

SQL> alter index SYS.I_SMB$CONFIG_PKEY rebuild;
alter index SYS.I_SMB$CONFIG_PKEY rebuild
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_WRI$_OPTSTAT_IND_OBJ#_ST' or partition of such index is in unusable state

To Fix:

SQL> alter index SYS.I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild;

Index altered.

SQL> alter index SYS.I_SQL$TEXT_PKEY rebuild;

Index altered.

ORA-28650: Primary index on an IOT cannot be rebuilt

SQL> alter index SYS.SQLLOG$_PKEY rebuild online;
alter index SYS.SQLLOG$_PKEY rebuild online
*
ERROR at line 1:
ORA-28650: Primary index on an IOT cannot be rebuilt

To Fix:


SQL> select table_name from dba_indexes where index_name='SQLLOG$_PKEY';

TABLE_NAME
--------------------------------------------------------------------------------
SQLLOG$

SQL> alter table SQLLOG$ move tablespace sysaux online; -- online only work if no iot_overflow

Table altered.