Re: Table with CLOB allocating lot of space

​Hi Hemant,

Currently the table is using 500gb ( lot of spaces ). The average size of the line is 4kb and it has 1.300.000 lines. I tried partitioned table and also partitioned 2 indexes. I said that I need rebuild index because I did not partitioned the PK (it has only an ID).

I am analyzing the scenary and also got that the table is created with PCTFREE equal to 40. It must be changed.

Now, I also realized that the LOGSEGMENT is with the normal size and the object that is using a lot space is the segment_type = 'TABLE'.

Thanks for your answer.

Regards
Eriovaldo

2018-05-14 0:46 GMT-03:00 Hemant K Chitale <hemantkchitale_at_gmail.com>:

> With option 2, you might also want to identify which of the Indexes can be
> Locally Partitioned with the table -- so that you do not need REBUILD for
> the Indexes when you do a DROP PARTITION. (If, as you say, *all*
> operations against the table are against today's data, any UPDATE / DELETE
> / SELECT operations would also be specifying the partition column date as a
> predicate --- meaning that you would not need Global Indexes)
>
> Why do you need to "release" the LOB space ? How big is it ? Is it
> continuously growing significantly even though you have DELETEs ?
>
> Hemant K Chitale
>
>
> Hemant K Chitale
>
>
>
> On Mon, May 14, 2018 at 5:06 AM, Eriovaldo Andrietta <
> ecandrietta_at_gmail.com> 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
>>
>>
>

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