ORA- Errors : ORA-01654: unable to extend index <SCHEMA_NAME>.<INDEX_NAME> by 128 in tablespace <TABLESPACE_NAME>

Problem

The application user reported ORA-01654: unable to extend index <SCHEMA_NAME>.<INDEX_NAME> by 128 in tablespace <TABLESPACE_NAME>

Root Cause

Oracle could not find a large enough area of free contiguous space in which to fit the next extent in tablespace.

Oracle Documentation

The following is suggestion from the Oracle Documentation.

01654, 00000, "unable to extend index %s.%s by %s in tablespace %s"
// *Cause:  Failed to allocate an extent of the required number of blocks for
//          an index segment in the tablespace indicated.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
//          files to the tablespace indicated.

Solution

The possible solution are :

- Add a datafile: 
        ALTER TABLESPACE <tablespace name> ADD DATAFILE '<full path and file name>' SIZE <integer> <k|m>; 

- Resize the datafile: 
        ALTER DATABASE DATAFILE '<full path and file name>' RESIZE <integer> <k|m>; 

- Enable autoextend: 
        ALTER DATABASE DATAFILE '<full path and file name>' AUTOEXTEND ON MAXSIZE []<integer> <k|m> |UNLIMITED];
- Manually coalesce adjacent free extents:
        ALTER TABLESPACE <tablespace name> COALESCE;
  The extents must be adjacent to each other for this to work.

 

References

  • Overview Of ORA-01654: Unable To Extend Index %s.%s By %s In Tablespace %s (Doc ID 146595.1)