My Oracle Support Banner

Some SYS Tables have UNUSABLE INDEXES Preventing Critical Business Functionality (Doc ID 1119734.1)

Last updated on JANUARY 08, 2025

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform. ***  ***
***Checked for relevance on 21-Jul-2017***

AWR
SYSAUX
Extent Management
Unusable Indexes
Function based Indexes

Symptoms

  • Indexes become UNUSABLE affecting base functionality for some SYS objects
  • Indexes, Tables or objects in the SYSAUX tablespace may show errors allocating space or extents
  • The problem object may belong to AWR based WRI$ or WRH$ objects
  • You may find messages in the ALERT.LOG indicating failures to extend for various objects including Indexes or Tables
  • You may also find references to datafiles or TEMP being unable to extend

Changes

SYSAUX Tablespace was unable to EXTEND:
This lead to several error messages including the following


07-Jun-2010 00:00:14 ORA-1688: unable to extend table SYS.WRH$_LATCH partition WRH$_LATCH_3166307943_7399 by 8 in tablespace SYSAUX
ORA-1688: unable to extend table SYS.WRH$_LATCH partition WRH$_LATCH_3166307943_7399 by 8 in tablespace SYSAUX

07-Jun-2010 03:01:32 ORA-1654: unable to extend index SYS.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST by 128 in tablespace SYSAUX
ORA-1654: unable to extend index SYS.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST by 1024 in tablespace SYSAUX

07-Jun-2010 05:05:28 ORA-1652: unable to extend temp segment by 128 in tablespace SYSAUX
ORA-1652: unable to extend temp segment by 128 in tablespace SYSAUX
ORA-1652: unable to extend temp segment by 128 in tablespace SYSAUX
ORA-1652: unable to extend temp segment by 128 in tablespace SYSAUX

07-Jun-2010 05:05:51 ORA-1652: unable to extend temp segment by 128 in tablespace SYSAUX
ORA-1652: unable to extend temp segment by 128 in tablespace SYSAUX
...
... etc

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Symptoms
Changes
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.