Thursday, November 14, 2013

ORA-1691: unable to extend lobsegment Message Only on Alert Log When Inserting to a Table with SecureFile

Similar to Basic LOB situation mentioned in the earlier post a server side (or alert log only) ora-1691 message appears when inserting to table with a securefile. The test was created using the same infrastructure as before so the tablespace names, table names and the java code are identical to the ones mentioned in earlier post. Therefore the test case used for the previous post could also be used here. Only difference is that lob segment is now a securefile.
CREATE TABLE lobtest ( ID number,  "OBJECT" BLOB ) SEGMENT CREATION IMMEDIATE TABLESPACE datatbs
   LOB
  (
    "OBJECT"
  )
  STORE AS securefile object_lob_seg (
                TABLESPACE lobtbs
                DISABLE STORAGE IN ROW
        CACHE
                RETENTION NONE
        STORAGE (MAXEXTENTS UNLIMITED)
        INDEX object_lob_idx (
            TABLESPACE lobtbs
            STORAGE (MAXEXTENTS UNLIMITED)
        )
    )
/
In the previous case the ora-1691 was logged on the alert log during the subsequent inserts. But with the securefile table ora-1691 is raised in the initial insert itself and well before the tablespace is exhausted.
Using the tablespace from the previous test case (each with maximum of 10M) 143 rows could be inserted (different to maximum value in the earlier case. This could be due to use of securefile) before the client side ora-1691 is shown. But at the point of inserting the 133rd row and forward ora-1691 is logged on the alert log but all rows are inserted successfully. Use of retention none has no effect in removing or reducing this logging. Similar to previous case, unless number of the rows inserted somehow known by other means it would be difficult to know any row insertion failed due to a space issue.
Few SR updates later issue is being investigated as a possible bug. Post will be updated with the outcome.

Related Post
ORA-1691: unable to extend lobsegment is expected behavior?




Update 06 December 2013
SR is being inactivated and issue is to be tracked with
Bug 17463217 : ORA-1691: UNABLE TO EXTEND LOBSEGMENT SHOWN ON ALERT LOG WHEN INSERTING TO TABLE

Update 02 January 2014
Oracle's reply to the SR was that this is not a bug but expected behavior. The explanation is when inserting around 133rd row mark oracle process realize there's not enough in the table so it allocate some extents to the table and insert is successful, as such no error on the client side. However at this time oracle process also identifies that space pressure exits for this table and starts background process to preallocate further extents to the table. This is done by the Space Management Slave Process (Wnnn) which "performs various background space management tasks, including proactive space allocation and space reclamation". When this slave process tries to preallocate it gets the ora-1691 as there's not enough free space in the data file, because pre-allocation of extents goes beyond the maximum size for the data file. Therefore slave process logs ora-1691 on alert log. End of explanation.
So if there's any ora-1691 on alert log it's worth while to check the application logs as well to check if any row insertion failed, since as shown here it is possible to get ora-1691 on alert log but all rows to successfully get inserted as well.