0

An Oracle 12c production database includes tables with a BLOB column. Usually the growth rate is 2GB per week. This is the table create statement:

CREATE TABLE XXX.FILEENTRY
(
  FILENAME     VARCHAR2(300 CHAR),
  FILESIZE     NUMBER(20),
  FILECONTENT  BLOB
)
LOB (FILECONTENT) STORE AS SECUREFILE (
  TABLESPACE  DATA
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  CACHE
  LOGGING
      STORAGE    (
                  INITIAL          104K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                  PCTINCREASE      0
                  BUFFER_POOL      DEFAULT
                 ))
TABLESPACE DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
MONITORING;

Since some days the LOB increases up to 100GB a day. The LOB which increases that fast is: SYS_LOB0000119621C00011$$:

Owner,Segment name,Segment size (GB)
xxx,FILEENTRY,1,8
xxx,SYS_LOB0000119621C00011$$, 1156,1

The application has a transfer table which only shows the usual input of about 2GB a week. What could be the reason for this growth if this is not coming from the users? Is there a way to check the content of SYS_LOB0000119621C00011$$?

  • What makes you think the data isn't coming from the users? There's no reason the database would allocate space it doesn't need. – Andrew Brennan Jan 7 at 11:08
  • 1
    @AndrewBrennan That is how software would work, ideally. Actually, there is only 1 platform on which Oracle works flawlessly, which is: Powerpoint. – Balazs Papp Jan 7 at 11:46
  • @BalazsPapp Sure there are sometimes bugs, but that doesn't neccessarily mean this is a bug, it may be expected behaviour – Andrew Brennan Jan 7 at 11:54
2

Is there a way to check the content of SYS_LOB0000119621C00011$$?

Yes you can first look at dba_lobs view to check which table/column is associated with this segment.

What could be the reason for this growth if this is not coming from the users?

Maybe one huge file has been inserted in your table. You can use DBMS_LOB.GETLENGTH(column_name) to find the size of each lob.

select filename, DBMS_LOB.GETLENGTH(filecontent) from fileentry order by 2;
2

Have a look at (Oracle Support content, requires subscription):

LOB segment size is significantly increasing despite of small actual size of data in it (Doc ID 2326423.1)

There are 3 bugs in it that have one-off patches for 12c (they are fixed in 18c):

  • Bug 26007010 : DATABASE GREW EVEN THOUGH RESOURCES DELETED
  • Bug 26439748 : ENQ: TX - CONTENTION AND GC BUFFER BUSY ACQUIRE WAITS ON SECUREFILES IN 12.1
  • Bug 26513067 : ASSIGNMENT OF INSTANCE AFFINITY FLAWED IN KTSLA_HBB_UPDATE_CACHE

And some hidden parameters as well to set.

Here is the public version of that note (not the full content):

LOB segment size is significantly increasing despite of small actual size of data in it (Doc ID 2326423.1)

SYMPTOMS

  • A LOB segment is of size around few hundreds of GB when it is actually of around few hundred of MB only.
  • The bug fixes can only be applied on Linux. No fix for other platforms yet. – r0tt Jan 11 at 9:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.