Q
Problem solve Get help with specific problems with your technologies, process and projects.

What's causing unable to extend lob segment error?

I have created f/wg table and tablespaces in my database. After inserting about 2000 records, it gives me an error...

that it is unable to extend lob segment. What would be the possible cause and remedy. This tablespace is used by other tables but they don't have any records.

create tablespace TS_AUD_GEN datafile
  '/oradata/nsdl/ts_aud_gen.1' size   500M
  extent management local
  uniform size 10M;

DROP Table AUD_DTL_IMG_TBL  CASCADE CONSTRAINTS;

CREATE TABLE AUD_DTL_IMG_TBL
(
  TXN_NO       NUMBER (9) 			           NOT NULL,
  SUB_SRL_NO   NUMBER(9)                                   NOT NULL,
  OLD_VAL      CLOB                                        NOT NULL,
  NEW_VAL      CLOB                                        NOT NULL
)
TABLESPACE TS_AUD_GEN
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          10M
            NEXT             10M
            MINEXTENTS       1
            MAXEXTENTS       1000
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING 
  LOB (OLD_VAL) STORE AS 
      ( TABLESPACE  TS_AUD_GEN
        ENABLE      STORAGE IN ROW
        CHUNK       8192
        PCTVERSION   10
        NOCACHE
        STORAGE    (
                    INITIAL          10M
                    NEXT             10M
                    MINEXTENTS       1
                    MAXEXTENTS       1000
                    PCTINCREASE      0
                    FREELISTS        1
                    FREELIST GROUPS  1
                    BUFFER_POOL      DEFAULT
                   )
      )
  LOB (NEW_VAL) STORE AS 
      ( TABLESPACE  TS_AUD_GEN
        ENABLE      STORAGE IN ROW
        CHUNK       8192
        PCTVERSION  10
        NOCACHE
        STORAGE    (
                    INITIAL          10M
                    NEXT             10M
                    MINEXTENTS       1
                    MAXEXTENTS       1000
                    PCTINCREASE      0
                    FREELISTS        1
                    FREELIST GROUPS  1
                    BUFFER_POOL      DEFAULT
                   )
      )
NOCACHE
NOPARALLEL
/

You've told the LOB segments to be stored in the same tablespace as the data itself. You may want to consider storing the LOB segments in a tablespace all to themselves and simply define that tablespaces storage parameters the way you want them. Also, what is the size limit on the TS_AUD_GEN tablespace? If it says it can't extend, are you absolutely sure that you have enough disk space available for the allocations you're requesting here? Also, you mention adding 2000 records before you hit the problem. Have you checked to see how much space is allocated vs. available on disk somewhere before you hit the limit?

What is the typical size of a single row. With two CLOB columns, you could theoretically store 8GB+ in a single row. If you're storing large amounts of data which are allocating 10M extents with MAXEXTENTS set to 1000, you've got 10M * 1000 as your limit. This is around 10 GB (10000 MB). A couple of big rows and you're done!

Bottom line, it looks like you need to review your data and the storage parameters for accuracy.

This was last published in December 2003

Dig Deeper on Oracle error messages

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close