Re: ORA-1691, yet a few bytes are written in the LOB of the table
From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Wed, 10 Jun 2009 06:57:02 -0700 (PDT)
Message-ID: <2af814cd-5ce7-48fb-9439-73634daf2704_at_r3g2000vbp.googlegroups.com>
On Jun 8, 2:28 pm, Sébastien de Mapias <sglrig..._at_gmail.com> wrote:
> Hi,
>
> For a little more than 24 hours we've been having
> - "ORA-1691: unable to extend lobsegment XX by 64 intablespace
> -
> <lob tbsp>..."
> in our alert log file (tablespace autoextend clause has been
> modified now), but we noticed that in this table the insert
> statements that took place after the error started appearing worked
> and even that data was partly created in its only LOB column: it
> succeeded for a few bytes only (we store PDF files: we can open
> them and see the first few lines of every document made since
> yesterday)...
>
> We are in 10g R2, and the table is defined with a LOB column
> that has its own tablespace (where we had this problem).
>
> How come Oracle wrote a few bytes each time and then failed
> with "ORA-1691: unable to extend lobsegment ..." and commited
> the record insert ??
>
> Thanks.
> Seb
Date: Wed, 10 Jun 2009 06:57:02 -0700 (PDT)
Message-ID: <2af814cd-5ce7-48fb-9439-73634daf2704_at_r3g2000vbp.googlegroups.com>
On Jun 8, 2:28 pm, Sébastien de Mapias <sglrig..._at_gmail.com> wrote:
> Hi,
>
> For a little more than 24 hours we've been having
> - "ORA-1691: unable to extend lobsegment XX by 64 intablespace
> -
> <lob tbsp>..."
> in our alert log file (tablespace autoextend clause has been
> modified now), but we noticed that in this table the insert
> statements that took place after the error started appearing worked
> and even that data was partly created in its only LOB column: it
> succeeded for a few bytes only (we store PDF files: we can open
> them and see the first few lines of every document made since
> yesterday)...
>
> We are in 10g R2, and the table is defined with a LOB column
> that has its own tablespace (where we had this problem).
>
> How come Oracle wrote a few bytes each time and then failed
> with "ORA-1691: unable to extend lobsegment ..." and commited
> the record insert ??
>
> Thanks.
> Seb
I'd bet the table was created with ENABLE STORAGE IN ROW, which is default unless explicitly overridden. Until the LOB hits approx. 4000 bytes (minus control information) it is stored in the table segment, not the lobsegment. The fine manual says that LOBs are stored out of line if their size exceeds 4000 bytes regardless this option. If LOB inline storage is enabled, Oracle starts to write the data inline and if the data size eventually exceeds the 3964 bytes Oracle migrates it to the lobsegment. However it appears that the data already written inline persists if this migration fails.
Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
Received on Wed Jun 10 2009 - 08:57:02 CDT