oracle ora 01691,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.