Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Lob segment consuming too much space (long post, please read)

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

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Fri, 1 Nov 2002 23:03:08 +1000
Message-ID: <sTtw9.67183$g9.189417@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 - 07:03:08 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US