From the Admin Guide "Automatic tuning of undo retention is not supported for LOBs. This is because undo information for LOBs is stored in the segment itself and not in the undo tablespace. For LOBs, the database attempts to honor the minimum undo retention period specified by UNDO_RETENTION. However, if space becomes low, unexpired LOB undo information may be overwritten."
From Database Concept "The database manages read consistency for LOB segments differently from other data. Instead of using undo data to record changes, the database stores the before images in the segment itself. When a transaction changes a LOB, the database allocates a new chunk and leaves the old data in place. If the transaction rolls back, then the database rolls back the changes to the index, which points to the old chunk."
Below is the test case that illustrate the reporting of ora-1691 on the alert log. The test case create the LOB as BasicFile. But the observed behavior is same for SecureFile as well. The test was done on 11.2.0.3.7 and 11.1.0.16 on Linux.
1. Create two tablespaces one for data and one for lob segments
SQL> create tablespace datatbs datafile '+data(datafile)' size 5m autoextend on next 1m maxsize 10m; SQL> create tablespace lobtbs datafile '+data(datafile)' size 5m autoextend on next 1m maxsize 10m;2. Create the table with a LOB column and make sure data segments and lob segments are created in the correct tablespaces
CREATE TABLE lobtest ( ID number, "OBJECT" BLOB ) SEGMENT CREATION IMMEDIATE TABLESPACE datatbs
LOB ( "OBJECT" )
STORE AS object_lob_seg ( TABLESPACE lobtbs
DISABLE STORAGE IN ROW
CHUNK 8K
CACHE
INDEX object_lob_idx (TABLESPACE lobtbs)
);3. The java code given at the end of the post is used to populate the table. The java code will insert a integer and a 50K BLOB.java LobReuse 192.168.0.66 1521 ent11g2 asanga asa 141Here (192.168.0.66 1521 ent11g2) is the (DB Server name or IP, listener port, SID) and next two parameters (asanga asa) is (username, password) and last parameter 141 is number of inserts. Verify 141 rows are inserted
SQL> select count(*) from lobtest;
COUNT(*)
----------
141141 is the maximum number of inserts that could be made before the 10m maximum size is reached on the LOBTBS tablespace. Trying to insert any more rows would result in ora-1691 on client side which is the expected behavior (this is not the reason for the post).java LobReuse 192.168.0.66 1521 ent11g2 asanga asa 1
java.sql.SQLException: ORA-01691: unable to extend lob segment ASANGA.OBJECT_LOB_SEG by 128 in tablespace LOBTBS
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)and following lines will be reported on the alert logORA-1691: unable to extend lobsegment ASANGA.OBJECT_LOB_SEG by 128 in tablespace LOBTBS ORA-1691: unable to extend lobsegment ASANGA.OBJECT_LOB_SEG by 128 in tablespace LOBTBS ORA-1691: unable to extend lobsegment ASANGA.OBJECT_LOB_SEG by 128 in tablespace LOBTBS4. Delete all the inserted rows and verify no rows are in the table.
SQL> delete from lobtest;
141 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from lobtest;
COUNT(*)
----------
0Deleting rows will not reduce the segment size (unless shrink space other space reclaimable mechanism are used to reduce the size). Once space is allocated to a segment it will remain allocated and reused if become free due to row deletion. At this stage the segment is occupying the full tablespace and there's no place to grow and for new inserts the log segment space must be reused.5. Run the java code to insert another 141 rows as before.
java LobReuse 192.168.0.66 1521 ent11g2 asanga asa 141This will execute without any errors and at the end of the execution 141 rows would have been inserted to the table
SQL> select count(*) from lobtest;
COUNT(*)
----------
141However if alert log was monitored during this time following could be seenORA-1691: unable to extend lobsegment ASANGA.OBJECT_LOB_SEG by 128 in tablespace LOBTBS ORA-1691: unable to extend lobsegment ASANGA.OBJECT_LOB_SEG by 128 in tablespace LOBTBS ORA-1691: unable to extend lobsegment ASANGA.OBJECT_LOB_SEG by 128 in tablespace LOBTBS ORA-1691: unable to extend lobsegment ASANGA.OBJECT_LOB_SEG by 128 in tablespace LOBTBS ORA-1691: unable to extend lobsegment ASANGA.OBJECT_LOB_SEG by 128 in tablespace LOBTBS ORA-1691: unable to extend lobsegment ASANGA.OBJECT_LOB_SEG by 128 in tablespace LOBTBSThis is misleading as no error was shown on the client side and 141 rows got inserted successful. But there's no way to verify this looking only at the alert log which looks like the segment is unable to expand and no rows got inserted. Having the Event 44951 set seem to reduce the number of lines reporting ora-1691 on alert log but still it does get reported. (Event 44951 sets number of chunks to clean up). Beside the error it enq: HW - contention waits were also observed.
According to Oracle this is expected behavior and not a bug. For system where automated alert log monitoring is in place it would be difficult to distinguish if there's a space issue or not without looking into free space within the lobsegment.
One way to overcome this observed behavior is to change the undo related parameters of the LOB segment, which are pctversion and retention. Creating the table with pctversion 0 eliminate the reporting of ora-1691 on the alert log when log segment space is reused.
CREATE TABLE lobtest ( ID number, "OBJECT" BLOB ) SEGMENT CREATION IMMEDIATE TABLESPACE datatbs
LOB ( "OBJECT" )
STORE AS object_lob_seg ( TABLESPACE lobtbs
DISABLE STORAGE IN ROW
CHUNK 8K
CACHE
PCTVERSION 0
INDEX object_lob_idx (TABLESPACE lobtbs)
);However this may not suite every application and could lead to ora-1555 snapshot too old errors. Oracle documentation provide some guide lines on how to set pctversion value based on update/read patterns of the application.If space is not reused within the the undo_retention then again the ora-1691 is not observed on the alert log. There's no explicit way to change the retention value on LOB segment. It's taken from the undo_retention parameter.
Related Post
ORA-1691: unable to extend lobsegment Message Only on Alert Log When Inserting to a Table with SecureFile
Java code used for LOB insert
import java.sql.Connection;
import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.pool.OracleDataSource;
/**
*
* @author Asanga
*/
public class LobReuse {
public static void main(String[] args) {
try {
OracleDataSource pool = new OracleDataSource();
pool.setURL("jdbc:oracle:thin:@"+args[0]+":"+args[1]+":"+args[2]);
pool.setUser(args[3]);
pool.setPassword(args[4]);
int inserts = Integer.parseInt(args[5]);
for(int i = 0 ; i < inserts ; i++){
Connection con = pool.getConnection();
con.setAutoCommit(false);
byte[] x = new byte[50 * 1024];
x[1]=10; x[40 * 1024] = 20;
OraclePreparedStatement pr = (OraclePreparedStatement) con.prepareStatement("insert into lobtest values(?,?)");
pr.setInt(1, i );
pr.setBytes(2, x);
pr.execute();
con.commit();
pr.close();
con.close();
}
} catch (Exception ex) {
ex.printStackTrace();
}
}}
