ORA-01654 unable to extend index string.string by string in tablespace string

I got ” ORA-01654 unable to extend index string.string by string in tablespace string ”  error in Oracle database.

ORA-01654 unable to extend index string.string by string in tablespace string

Details of error are as follows.

ORA-01654 unable to extend index string.string by string in tablespace string

Cause: Failed to allocate an extent for index segment in tablespace.

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

unable to extend index string.string by string in tablespace string

This ORA-01654 error is related with the failed to allocate an extent for index segment in tablespace.

1. In order to see the free space available for a particular tablespace, you must use the view DBA_FREE_SPACE. Within this view, each record represents one fragment of space. How the view DBA_FREE_SPACE can be used to determine
the space available in the database.

2. The DBA_INDEXES view describes the size of next extent (NEXT_EXTENT) and the percentage increase  (PCT_INCREASE) for all indexes in the database. The “next_extent” size is the size of extent that is trying to be allocated (and for which you have the error).

When the extent is allocated :
next_extent = next_extent * (1 + (pct_increase/100))

To solve this error, you can perform the following solutions.

1- Add a datafile to related tablespace as follows.

ALTER TABLESPACE <tablespace name> ADD DATAFILE '<full path and filename>' SIZE <integer> <k|m>;

ALTER TABLESPACE INDEX_TBS ADD DATAFILE '+DATA' SIZE 10G;

2- Manually coalesce adjacent free extents as follows.The extents must be adjacent to each other for this to work.

ALTER TABLESPACE <tablespace name> COALESCE;

3- You can resize the existing datafile as follows.

ALTER DATABASE DATAFILE '<full path and file name>' RESIZE <integer> <k|m>;

4- You can enable autoextend for the datafiles of this tablespace as follows.

ALTER DATABASE DATAFILE '<full path and file name>' AUTOEXTEND ON MAXSIZE UNLIMITED;

5- You can defragment the Tablespace as follows. Lower “next_extent” and/or “pct_increase” size:

ALTER <segment_type> <segment_name> STORAGE ( next <integer> <k|m> pctincrease <integer>);

Do you want to learn Oracle Database for Beginners, then read the following articles.