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.
