Table with CLOB allocating lot of space

From: Eriovaldo Andrietta <ecandrietta_at_gmail.com>
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

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
Received on Sun May 13 2018 - 23:06:23 CEST

Original text of this message