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.
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.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments