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@xxxxxxxxx>
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 ways to release the space ?
Regards
Eriovaldo
