Solution to: ORA-1691: unable to extend lobsegment

This ORA happens allways a lob segement cannot extended itself to fit the new data being inserted.
Tipical solution is extending the data file and adding a datafile, but sometimes, not often, error only apears in alert log (no client side) when space taken by the lob segment is allocated for reuse, before the undo retention period is expired.

Now comes the tip y maybe you dont know, undo information for LOBs is stored in the segment itself and not in the undo tablespace, the database manages LOB segments differently from other data, undo data is stored the undo data, the before images in the same segment, if transaction rolls back, the database a changes is made in the index pointing to the old data.
Lets test:

1. we create a tablespaces for data and another for lob segment
SQL> create tablespace tbdata datafile ‘+DATA’ size 10m autoextend on next 1m maxsize 20m;
SQL> create tablespace tblob datafile ‘+DATA’ size 10m autoextend on next 5m maxsize 20m;

2. We create a table with LOB column

CREATE TABLE testlob ( ID number, “OBJECT” BLOB ) SEGMENT CREATION IMMEDIATE TABLESPACE tbdata
LOB ( “OBJECT” )
STORE AS object_lob_seg ( TABLESPACE tblob
DISABLE STORAGE IN ROW
CHUNK 8K
CACHE
INDEX object_lob_idx (TABLESPACE tblob)
);
3. we populate the table with a java code that will insert 282 rows, a integer and a 50K BLOB.

java ReuseLob 192.168.10.10 1521 test11g2 oratest oracle 282

As you can see 192.168.10.10 1521 and test11g2 is ip, port and SID. This two parameters(oratest oracle) username and password
and number is number of inserts. Lets check we insert 282 new rows:

SQL> select count(*) from testlob;

COUNT(*)
———-
282

——- java code —————————

Java code used for LOB insert
import java.sql.Connection;
import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.pool.OracleDataSource;

public class ReuseLob {

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 testlob values(?,?)”);
pr.setInt(1, i );
pr.setBytes(2, x);
pr.execute();
con.commit();
pr.close();
con.close();
}

} catch (Exception ex) {
ex.printStackTrace();
}
}}
—————- cut here code ——————–

282 is the maximum number of inserts that could be made before the 20m maximum size is reached on the tblob tablespace. Trying to insert any more rows would result in ora-1691 on client side which is the expected behavior, so lets do this:

java ReuseLob 192.168.10.10 1521 test11g2 oratest oracle 10

java.sql.SQLException: ORA-01691: unable to extend lob segment ORATEST.OBJECT_LOB_SEG by 128 in tablespace tblob

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 log
ORA-1691: unable to extend lobsegment ORATEST.OBJECT_LOB_SEG by 128 in tablespace tblob
ORA-1691: unable to extend lobsegment ORATEST.OBJECT_LOB_SEG by 128 in tablespace tblob
ORA-1691: unable to extend lobsegment ORATEST.OBJECT_LOB_SEG by 128 in tablespace tblob
4. Delete all:

SQL> delete from testlob;
282 rows deleted.

SQL> commit;
Commit complete.

SQL> select count(*) from testlob;

COUNT(*)
———-
0

Cause we delete, segement size still same size (unless we shrink space), segement 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 282 rows again.

java ReuseLob 192.168.10.10 1521 test11g2 oratest oracle 282

no error with this execution and row inserted.

SQL> select count(*) from testlob;

COUNT(*)
———-
282

But if we check alert log:

ORA-1691: unable to extend lobsegment ORATEST.OBJECT_LOB_SEG by 128 in tablespace tblob
ORA-1691: unable to extend lobsegment ORATEST.OBJECT_LOB_SEG by 128 in tablespace tblob
ORA-1691: unable to extend lobsegment ORATEST.OBJECT_LOB_SEG by 128 in tablespace tblob
ORA-1691: unable to extend lobsegment ORATEST.OBJECT_LOB_SEG by 128 in tablespace tblob
ORA-1691: unable to extend lobsegment ORATEST.OBJECT_LOB_SEG by 128 in tablespace tblob
ORA-1691: unable to extend lobsegment ORATEST.OBJECT_LOB_SEG by 128 in tablespace tblob

No error on client side and 282 rows got inserted successful if u look alert looks like the segment is unable to expand and no rows got inserted, Oracle say is expected behavior and not a bug.

One solution is change pctversion to 0 eliminate the reporting of ora-1691 on the alert log when log segment space is reused.

alter table ORATEST.testlob modify lob (OBJECT_LOB_SEG) (pctversion 0);

Advertisements

Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s