Blog Compass Lite 2.0 - personal blog About Me Album Bookmarks

Monday, February 27, 2006

ORA-01691: unable to extend lob segment

Oracle Data File Temp Tempfile Datafile.

Problem Scenario:

Storing large binary file to Oracle server as BLOB gives the following error:

[contextRoot] ERROR [ExecuteThread: '10' for queue: 'weblogic.kernel.Default'] ActionExceptionHandler.logException(148) | org.springframework.orm.hibernate3.HibernateJdbcException: JDBC exception on Hibernate data access: Could not execute JDBC batch update; nested exception is java.sql.BatchUpdateException: ORA-01691: unable to extend lob segment TABLESPACE.SYS_LOB0000059665C00009$$ by 8 in tablespace TABLESPACE

java.sql.BatchUpdateException: ORA-01691: unable to extend lob segment TABLESPACE.SYS_LOB0000059665C00009$$ by 8 in tablespace TABLESPACE

Solution:

The problem was caused by insufficient space to extend a segment for the tablespace. Adding a datafile or adjusting the size of the current datafile of the tablespace can act as short-term solution.

Adding datafile:
ALTER TABLESPACE <tablespace_name> ADD DATAFILE ‘<path_of_datafile>’ SIZE 10M;

Enlarging the current datafile:
ALTER DATABASE DATAFILE ‘<path_to_datafile>’ RESIZE 10M;

(Assuming you are adding a 10MB datafile and resizing the datafile to 10MB respectively.)

You may run the following SQL statement to check the status of various datafiles:
SELECT file_name, tablespace_name, bytes/1024/1024 MB, AUTOEXTENSIBLE FROM dba_data_files;

and use the following SQL to turn on AUTOEXTEND option:
ALTER DATABASE DATAFILE ‘<path_to_datafile>' AUTOEXTEND ON;