ORA-01691錯誤分析

解答者：tolywang

[@more@]

在應用程式中插入oracle大欄位的時候出現如下提示:
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.

Problem Description:
====================

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
TEST
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.


Problem Explanation:
====================

The LOB segment associated with the target table has reached hit
a limit or run out of sufficiently large chunks of contigous space.


Search Words:
=============

loc clob import

Solution Description:
=====================

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
from dba_segments
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:

select bytes
from dba_free_space
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
to complete.

Note that you would need to specify ignore=y in the import options to avoid
failing on the primary key constraint.


Solution Explanation:
=====================

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.

還有可能是bug .

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 8.1.5.2 patchset released. However, there is an individual patch for this bug which can be applied to 8.1.5.0 and 8.1.5.1 on Solaris. You will need to log an iTAR in order to obtain the patch as it is not available for download from MetaLink.