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 220.127.116.11.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?