Re: Table with CLOB allocating lot of space

Hi Gogala and Martin,

Thanks for your answer.​
I will consider all comments.

Tks.
Eriovaldo

2018-05-15 13:24 GMT-03:00 Mladen Gogala <gogala.mladen_at_gmail.com>:

> 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.
> Regards
>
>
> On 05/13/2018 05:06 PM, Eriovaldo Andrietta wrote:
>
> Hello,
>
> 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 update).
>
> 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 SHRINK SPACE;
> alter table HISTORICO_WS_PCT10 DISABLE row movement;
>
> I saw that ​also can be used :
>
> ​
> ALTER TABLE HISTORICO_WS_PCT10 MODIFY LOB(xml_request) (SHRINK SPACE
> CASCADE);
> ALTER TABLE HISTORICO_WS_PCT10 MODIFY LOB(xml_request) (SHRINK SPACE);
>
>
>
> 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.
>
> CREATE OR REPLACE PROCEDURE ERI_TST_PCTFREE10
> AS
> VAR1 LONG;
> VAR2 VARCHAR2(4000);
> vComando VARCHAR2(4000);
> vDateInsert DATE;
> vDateDelete DATE;
>
> BEGIN
> DBMS_OUTPUT.ENABLE(NULL);
> vDateInsert := TRUNC(sysdate);
> vDateDelete := vDateInsert;
> DBMS_OUTPUT.PUT_LINE ('Data Insert : ' || to_char(vDateInsert,
> 'dd-mm-yyyy'));
> DBMS_OUTPUT.PUT_LINE ('Data Delete : ' || to_char(vDateDelete,
> 'dd-mm-yyyy'));
> FOR p in (SELECT * FROM USER_TAB_PARTITIONS
> WHERE partition_name != 'HIST_DATE'
> )
> LOOP
> SELECT high_value INTO VAR1 FROM USER_TAB_PARTITIONS WHERE
> 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
> vComando := 'ALTER TABLE HIST_WS_PCT10 DROP PARTITION ' ||
> p.partition_name;
> dbms_output.put_line (' vComando : ' || vComando);
> EXECUTE IMMEDIATE vComando;
> END IF;
> END LOOP;
> FOR p in (SELECT * FROM USER_INDEXES
> WHERE status = 'UNUSABLE'
> )
> LOOP
> vComando := 'ALTER INDEX ' || p.INDEX_NAME || ' REBUILD
> ONLINE';
> EXECUTE IMMEDIATE vComando;
> END LOOP;
> COMMIT;
> END;
> /
>
> In this case, I realized that I need to rebuild Indexes that were UNUSED.
>
>
> I am thinking to use option 2.
>
> ​My doubt is :
>
> Are there another way​s to release the space ?
>
>
> Regards
> Eriovaldo
>
>
> --
> Mladen Gogala
> Database Consultant
> Tel: (347) 321-1217
>
>

--
http://www.freelists.org/webpage/oracle-l