Table with CLOB allocating lot of space
Date: Sun, 13 May 2018 18:06:23 -0300
Message-ID: <CAJdDhaMVSyjbDoeWUN2h-yFWSQMRw9=vmrR1Wf=9QCoi3h5jLA_at_mail.gmail.com>
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
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.
CREATE OR REPLACE PROCEDURE ERI_TST_PCTFREE10
AS
BEGIN
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;
ALTER TABLE HISTORICO_WS_PCT10 MODIFY LOB(xml_request) (SHRINK SPACE
CASCADE);
ALTER TABLE HISTORICO_WS_PCT10 MODIFY LOB(xml_request) (SHRINK SPACE);
VAR1 LONG;
VAR2 VARCHAR2(4000);
vComando VARCHAR2(4000);
vDateInsert DATE;
vDateDelete DATE;
DBMS_OUTPUT.ENABLE(NULL);
vDateInsert := TRUNC(sysdate);
vDateDelete := 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 ways to release the space ?
Regards
Eriovaldo
-- http://www.freelists.org/webpage/oracle-lReceived on Sun May 13 2018 - 23:06:23 CEST