Oracle Database LOB increases untypical

bloboracle

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$$?

Best Answer

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;

Related Solutions

How to determine Oracle LOB storage footprint

You can get the size in bytes of the BLOB column via DBMS_LOB.GETLENGTH(...).

To get a total size in bytes of column for the entire table SUM it up like this:

SELECT SUM(DBMS_LOB.GETLENGTH(my_blob_column)) as total_bytes
FROM my_table

If you've marked the BLOB column as deduplicated then you can run something like the following to get stats on the actual size in bytes used. Compare the used_bytes value before/after your enable settings that could change the size of the BLOB column (eg. dedupe, compression, etc).

declare  
    p_schema_name           varchar(256) DEFAULT 'THE_SCHEMA_NAME';
    p_table_name            varchar(256) DEFAULT 'THE_TABLE_NAME';
    --
    l_segment_name          varchar2(30);
    l_segment_size_blocks   number; 
    l_segment_size_bytes    number; 
    l_used_blocks           number;  
    l_used_bytes            number;  
    l_expired_blocks        number;  
    l_expired_bytes         number;  
    l_unexpired_blocks      number;  
    l_unexpired_bytes       number;  

begin
    FOR rec IN (select segment_name 
                from dba_lobs 
                where owner = p_schema_name
                  and table_name = p_table_name
                order by 1)
    LOOP
        dbms_output.put_line('Segment Name=' || rec.segment_name);

        dbms_space.space_usage( 
            segment_owner           => p_schema_name,  
            segment_name            => rec.segment_name, 
            segment_type            => 'LOB', 
            partition_name          => NULL, 
            segment_size_blocks     => l_segment_size_blocks, 
            segment_size_bytes      => l_segment_size_bytes, 
            used_blocks             => l_used_blocks, 
            used_bytes              => l_used_bytes, 
            expired_blocks          => l_expired_blocks, 
            expired_bytes           => l_expired_bytes, 
            unexpired_blocks        => l_unexpired_blocks, 
            unexpired_bytes         => l_unexpired_bytes 
        );   


        dbms_output.put_line('  segment_size_blocks       => '||  l_segment_size_blocks);
        dbms_output.put_line('  segment_size_bytes        => '||  l_segment_size_bytes);
        dbms_output.put_line('  used_blocks               => '||  l_used_blocks);
        dbms_output.put_line('  used_bytes                => '||  l_used_bytes);
        dbms_output.put_line('  expired_blocks            => '||  l_expired_blocks);
        dbms_output.put_line('  expired_bytes             => '||  l_expired_bytes);
        dbms_output.put_line('  unexpired_blocks          => '||  l_unexpired_blocks);
        dbms_output.put_line('  unexpired_bytes           => '||  l_unexpired_bytes);
    END LOOP;
end;
/

This code is a slightly modified version of the code in Oracle's post explaining how deduplication works. The original is available here: http://www.oracle.com/technetwork/articles/sql/11g-securefiles-084075.html.

LOB indexes corruption in Oracle

The alert log should tell you if a data block is corrupt, no need to validate index structure. That will lock your table until completed. If no backups you might try to exporting the data using expdp. Then drop the table and re-create it and import using impdp. You will need to create the indexes, constraints and grants as well. Could also use CTAS.

Related Question