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

From: Anssi Huttunen <huttunen_at_no.spam.cs.tut.fi>
Date: Thu, 31 Oct 2002 19:58:36 +0200
Message-ID: <aprr0a$je5$1@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 Thu Oct 31 2002 - 11:58:36 CST