Re: Table with CLOB allocating lot of space

  • From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 15 May 2018 12:24:38 -0400

You should always use SECUREFILES. Also, use large uniform extents, don't do auto-allocate. LOB segments are actually files. With LOB segments, Oracle sort of creates a file system within a tablespace, with each LOB column being a file. What you have is an analogy to a file system, with a gazillion files. Theoretically, you could even use BFILE data type, but unfortunately most file systems are not programmed so that directories can hold hundreds of thousands of file names. And if you try that, you fill find out that the files consume quite large chunks of space. I've had the best experience with 32M uniform extents. My LOB files were images. With CLOB columns you can probably get away with smaller extents, like 8M.

On 05/13/2018 05:06 PM, Eriovaldo Andrietta wrote:


    I have a table with 2 columns CLOB.

    The pctfree and the pctused are equal to 10.

    On this table are executed all DML operations (insert, delete and

    I am looking for the best way to release the space of the LOBSEGMENT.
    It must be  ONLINE, I cannot drop the table and I the solution
    must be implemented to be executed scheduled to run once a day.

Option 1 : shrink

    alter table HISTORICO_WS_PCT10 enable row movement;
    alter table HISTORICO_WS_PCT10 shrink space cascade;
    alter table HISTORICO_WS_PCT10 DISABLE row movement;

I saw that ​also can be used :


Option 2: partition

I can also to create a partitioned table , because the application uses only the data related to the current day.
So, I think it is possible to drop the partition created by RANGE (date), using sysdate - 1.

    VAR1 LONG;
    VAR2 VARCHAR2(4000);
    vComando VARCHAR2(4000);
    vDateInsert   DATE;
    vDateDelete   DATE;

      vDateInsert := TRUNC(sysdate);
    vDateDelete := vDateInsert;
    DBMS_OUTPUT.PUT_LINE ('Data Insert : ' || to_char(vDateInsert,
    DBMS_OUTPUT.PUT_LINE ('Data Delete : ' || to_char(vDateDelete,
           WHERE partition_name != 'HIST_DATE'
    partition_name = p.partition_name;
     VAR2 := SUBSTR(VAR1, 1, 4000);
     dbms_output.put_line ('     Procurando : ' ||
    TO_CHAR(vDateDelete, 'SYYYY-MM-DD') || ' em  - ' || var2);
     IF INSTR(var2, TO_CHAR(vDateDelete, 'YYYY-MM-DD')) > 0  THEN
      dbms_output.put_line ('     vComando : ' || vComando);
               END IF;
            END LOOP;
         WHERE status = 'UNUSABLE'
      vComando := 'ALTER INDEX ' || p.INDEX_NAME || ' REBUILD ONLINE';
            END LOOP;

    In this case, I realized that I need to rebuild Indexes that were

I am thinking to use option 2.

​My doubt is :

    Are there another way​s to release the space ?


Mladen Gogala
Database Consultant
Tel: (347) 321-1217

Other related posts: