ORA-01691: unable to extend lob segment xxx.SYS_LOB0000030895C00010$$ by 2048 in tablespace xxx ORA-06512: at "xxx.storeprocedure", line 86 ORA-06512: at line 1
ORA-01691 unable to extend lob segment string.string by string in tablespace string
Cause: Failed to allocate an extent for LOB segment in tablespace.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.
You are attempting to insert or import data into a table containing
LOBs and get the following error:
ORA-01691: unable to extend lob segment TESTARCH.SYS_LOB#$ by X in tablespace
Cause: Failed to allocate an extent for lob segment in tablespace.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
files to the tablespace indicated.
The LOB segment associated with the target table has reached hit
a limit or run out of sufficiently large chunks of contigous space.
loc clob import
Assuming you had the following error:
ORA-1691: unable to extend lob segment TESTARCH.SYS_LOB0000004289C00007$
by 25600 in tablespace TEST
You would issue the following query:
select segment_type, bytes, extents,
initial_extent, next_extent, max_extents
where segment_name = 'SYS_LOB0000004289C00007$';
and extract the values for EXTENTS, NEXT_EXTENT, and MAX_EXTENTS from the
resulting record. Assuming these value were:
extents = 452
next_extent = 52428800
amx_extents = 999
You would then issue the following statement to determine if sufficient
space was available to extend the LOB segment:
where tablespace_name = 'TEST'
order by bytes desc;
If there was no contiguous block large enough to allocate the next extent,
you could try the following:
alter tablespace test coalesce
and subsequently rerun the above query. If there was still not enough space,
you would need to add a datafile to the TEST tablespace to allow the import
Note that you would need to specify ignore=y in the import options to avoid
failing on the primary key constraint.
This error should be handled no differently from other errors indicating the
inability to extend a database segment. Initial confusion may occur given the
unique nature of LOB storage.
metalink 上的回答 ：
The bug you mention is likely bug 855986 where a space leak can occur using LOB columns: deleted/updated LOB columns may not release the space occupied for reuse. This occurs for particular sizes of LOB. ORA-1691 is raised if the LOB cannot be extended.
The bug is fixed in 8.0.6 and 8.1.6. Although this fix was scheduled to be in it, unfortunately, there was no 184.108.40.206 patchset released. However, there is an individual patch for this bug which can be applied to 220.127.116.11 and 18.104.22.168 on Solaris. You will need to log an iTAR in order to obtain the patch as it is not available for download from MetaLink.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/166555/viewspace-790965/，如需转载，请注明出处，否则将追究法律责任。