Re: Table with CLOB allocating lot of space

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