Hi Gogala and Martin,
Thanks for your answer.
I will consider all comments.
Tks.
Eriovaldo
2018-05-15 13:24 GMT-03:00 Mladen Gogala <gogala.mladen@xxxxxxxxx>:
You should always use SECUREFILES. Also, use large uniform extents, don't
do auto-allocate. LOB segments are actually files. With LOB segments,
Oracle sort of creates a file system within a tablespace, with each LOB
column being a file. What you have is an analogy to a file system, with a
gazillion files. Theoretically, you could even use BFILE data type, but
unfortunately most file systems are not programmed so that directories can
hold hundreds of thousands of file names. And if you try that, you fill
find out that the files consume quite large chunks of space. I've had the
best experience with 32M uniform extents. My LOB files were images. With
CLOB columns you can probably get away with smaller extents, like 8M.
Regards
On 05/13/2018 05:06 PM, Eriovaldo Andrietta 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
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
