Re: Lob segment consuming too much space (long post, please read)

Richard,

It's about 770MB. So it's not that...

Anssi

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:sTtw9.67183$g9.189417_at_newsfeeds.bigpond.com...
> Hi Anssi,
>
> How much space does the lob index take ?
>
> Cheers
>
> Richard
> "Anssi Huttunen" <huttunen_at_no.spam.cs.tut.fi> wrote in message
> news:aprr6p$jnl$1_at_news.cc.tut.fi...
> >
> > I have ran into some strange problems, at least for me they are ;)
> >
> > I'm running Oracle 8.1.5 on WinNT 4.0 SP6. The application is such that
> > a few processes insert images into a BLOB column of a table and once
they
> > reach a certain age, one process deletes them. It is a sort of image
> > history database.
> >
> > For storing the images I have defined the following tablespace
> >
> > CREATE TABLESPACE img_images_ts
> > DATAFILE 'D:\Oracle\oradata\ImgDB\images01.dbf'
> > SIZE 100M AUTOEXTEND ON MAXSIZE 2000M
> > DEFAULT STORAGE (
> > INITIAL 5M NEXT 5M
> > MINEXTENTS 1 MAXEXTENTS UNLIMITED
> > PCTINCREASE 0
> > );
> >
> > After this, the tablespace has been altered to have 65 of similar
> > datafiles. So the max size is ~130GB.
> >
> > The table for the BLOBs is as follows.
> >
> > CREATE TABLE img_images (
> > ID NUMBER(10) REFERENCES img_events (ID) ON DELETE
> > CASCADE,
> > ImageType NUMBER(10) REFERENCES img_types (ID),
> > ImageBlob BLOB DEFAULT EMPTY_BLOB ()
> > )
> > TABLESPACE img_events_ts
> > LOB (ImageBlob) STORE AS (
> > TABLESPACE img_images_ts
> > DISABLE STORAGE IN ROW
> > NOCACHE
> > );
> >
> > After this the table has been altered so that the PCTVERSION was changed
> > to 0. It was 10 by default.
> >
> > The system has been running fine almost a year now, but suddenly I
> > started getting these errors:
> >
> > Error: Unspecified problem, ORA-01691: unable to extend lob segment
> > IMG.SYS_LOB0000012441C00003$$ by 2560 in tablespace IMG_IMAGES_TS
> >
> > I know what that means, but it should not happen:
> >
> > All the datafiles have grown to their max size, 2GB. The number of
> > extents for the lob segment multiplied by their size, amounts the total
> size
> > of
> > the datafiles. So it is clear there can be no more extents. The segment
is
> > as big as it should get.
> >
> > But when I run "... sum(dbms_lob.getlength (ImageBlob))...", I get ~85GB
> > in total! What is the ~45GB overhead? Where has the space gone?
> >
> > Question is: where have I gone wrong with the design of the tablespace
> > and lob segment. What is wrong with the above definitions and how could
I
> > analyze this problem more? In my opinion, no matter how wrong block size
> > or what I might have, should the overhead be so big. It's worse than a
FAT
> > file system ;)
> >
> > Any thoughts?
> >
> >
> > Regards,
> >
> > Anssi
> >
> >
> >
> >
>
>
Received on Fri Nov 01 2002 - 10:27:22 CST