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.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )