Re: Table with CLOB allocating lot of space

Hi Eriovaldo,

Which DB Version are you talking about?
Secure file or BasicFile LOBs?

In 12.2 you can even move Secure file LOB segments online.

But as your table segment is the big one, that's not so portant here. If you have proper license, I'd go with partitions.

Martin

Eriovaldo Andrietta <ecandrietta_at_gmail.com> schrieb am Mo., 14. Mai 2018, 15:22:

> ​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