ORA-01658 on index tablespace when trying to insert into empty table of new database

Question

We are getting ORA-01658 (Error :ORA-01658: unable to create INITIAL extent for segment in tablespace MYDATA_LDATA_INDEX) in our application logs. This happens when application is trying to insert data for the first time into tables that are empty. It is only happening to this particular index. The tablespace free space decreases by multiple of 50M.

The parameters used to create the index tablespace:

CREATE TABLESPACE MYDATA_LDATA_INDEX DATAFILE
  '/index/oradata/mydb/mydata_ldata_index_01.dbf'
SIZE 1300M AUTOEXTEND ON NEXT 10M MAXSIZE 2048M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

We are sure application is not trying to insert massive amount of data into the tables, an operation that only insert one row also causes this.

After increasing tablespace for the index manually, regular operation can be continued and tablespace usage for the index is not increasing dramatically.

We are using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production on Solaris 11.1.

How do we pin point source of the problem? Is there an issue with the parameter of the index tablespace?

Answer 1

Oracle docs note this about ORA-01658:

ORA-01658: unable to create INITIAL extent for segment in tablespace string

Cause: Failed to find sufficient contiguous space to allocate INITIAL extent for segment being created.

Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the tablespace or retry with a smaller value for INITIAL.

The obvious cure for the ORA-01657 error is to:

1. Make the INITIAL extent size smaller
2. Empty the recycle bin
3. Add a datafile the tablespace to provide a larger contiguous chunk size.
4. Reorganize the tables in the tablespace

Try this query for your tablespace maximum chunk size tsfree.sql.

This case of ORA-01658 concerns needing to make the tablespace bigger, which can be done by either extending your file, or adding another one. This is a good solution for ORA-01658 because repository tables need to be meticulously created keeping the extents of the table in mind.

This information was taken from ORA-01658: unable to create INITIAL extent tips from the dba-oracle.com website.

Disclaimer:
I am in no way affiliated with the company.