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.
