We got these errors for several LOB segments:
Thu Apr 21 15:04:46 2016
ORA-1691: unable to extend lobsegment ABC.SYS_LOB0000108726C00021$$ by 128 in tablespace ABC_DATA
Thu Apr 21 15:04:56 2016
ORA-1691: unable to extend lobsegment ABC.SYS_LOB0000108726C00021$$ by 128 in tablespace ABC_DATA
ORA-1691: unable to extend lobsegment ABC.SYS_LOB0000108726C00021$$ by 128 in tablespace ABC_DATA
ORA-1691: unable to extend lobsegment ABC.SYS_LOB0000108726C00021$$ by 128 in tablespace ABC_DATA
ORA-1691: unable to extend lobsegment ABC.SYS_LOB0000108726C00021$$ by 128 in tablespace ABC_DATA
This is 11.2.0.4 on Linux. The tablespace is locally managed with autoallocate. Block size is 8 KB. The autoextend is disabled. The tablespace consists of around 70 datafiles, 20 – 30 GB in size. Total tablespace size is more than 2 TB.
What is strange about these errors:
- At the time when these errors were reported the tablespace had around 400 GB free space (GB, not MB) while Oracle was unable to allocate 128 blocks = 1 MB
- DBA_EXTENTS shows that this tablespace has thousands of free extents 1 MB or more
- Some of the LOB segments that experienced this error are quite large. For example, one of them is around 90 GB in size, has more than 2,000 extents, and most of these extents are 64 MB. However all error messages show attempt to allocate 128 blocks = 1 MB, not 64 MB.
I found this blog post
http://asanga-pradeep.blogspot.com.au/2013/09/ora-1691-unable-to-extend-lobsegment-is.html
However I am not sure that it explains our issue:
- In this example the tablespace was full: 141 row was the max number of rows that could be inserted
- Then the rows were deleted and new rows were inserted, again 141
- Oracle needed to maintain images of old rows (undo) but there was no space to do that as tablespace was full
If I understood the test correctly if both times he tried to insert 70 rows there would have been no errors as tablespace would have enough space to accommodate both the old rows (as undo) and the new rows.
In our case the tablespace has heaps of free space so there should be no issues with accomodating both the old rows as undo and the new rows.