In Oracle How to shrink Securefile LOB tables

As per Oracle support, if the table uses securefile LOB the traditional shrink method ALTER TABLE MODIFY LOB (XMLRECORD) (SHRINK SPACE) will not shrink the LOB/table. The support recommends using the Online Redefinition method to shrink the table after purging or deletion activity. The same has been tested and sample steps have been shared below.

Step 1: We used the FBNK_CUSTOMER database, which has 5L records and 305 MB in size. The table uses Securefile LOB.

Step 2: Removing half of the records using the below delete command but the size remains the same after deletion.delete from FBNK_CUSTOMER where rownum <= 250000;

Step 3: Determine if the table can be redefined online.
SET SERVEROUTPUT ON
BEGIN
    DBMS_REDEFINITION.CAN_REDEF_TABLE('TAFJ','FBNK_CUSTOMER', DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

Step 4: Create the interim table

The redefinition procedure uses materialized view and fast refresh concept. In order to load the data from the original table an interim table has to be created. The interim and original tables must coexist until the redefinition is completed and the interim table is removed. As a result, there must be enough space in the tablespace(s) to hold both tables at that moment.

CREATE TABLE INTERIM AS SELECT * FROM TEST WHERE 1=2;

Step 5: Start the redefinition

BEGIN

DBMS_REDEFINITION.START_REDEF_TABLE(

             uname => 'TAFJ',

             orig_table => 'FBNK_CUSTOMER',

             int_table => 'INTERIM',

             options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);

END;

/

Step 6: Finish the redefinition

exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('TAFJ','FBNK_CUSTOMER','INTERIM');

Step 7: Recheck the size of the table after the redefinition

select bytes/1024/1024 MB from dba_segments where segment_name='FBNK_CUSTOMER';

Step 8: Drop the interim table and purge it from the recycle bin, if possible, purge the recycle bin to bring the disk usage down.

drop table interim purge ;

purge recyclebin ;

 

3. Compression option :

During the INTERIM table creation process (Step 4), you can also experiment with the compress option. Here is a sample DDL; the Bank DBA must make the appropriate adjustments to the environment.

CREATE TABLE "TAFJ"."INTERIM"

   ("RECID" VARCHAR2(255) NOT NULL ENABLE,

        "XMLRECORD" BLOB) SEGMENT CREATION DEFERRED

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 COMPRESS BASIC LOGGING

  TABLESPACE "T24DATA"

 LOB ("XMLRECORD") STORE AS SECUREFILE "LOB_TEST157" (

  TABLESPACE "T24DATA" ENABLE STORAGE IN ROW CHUNK 8192

  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES );

4. Notes to DBAs :

We have referred to the Oracle documents How to Shrink a Securefile LOB Using Online Redefinition (DBMS_REDEFINITION)? (Doc ID 1394613.1) and Primary Note: Overview of Online Redefinition of Tables (DBMS_REDEFINITION) (Doc ID 1357825.1) to successfully shrink the Securefiles LOB. If you have any concerns or difficulties while performing the procedure, please raise a case with Oracle Support and work with them to resolve the same.