Lets check how it works. My env is DB 19.20@Linux-x64
1) I created the table with 4 LOB columns of 4 different LOB types: BASICFILE BLOB, BASICFILE CLOB, SECUREFILE BLOB and SECUREFILE CLOB.
2) Filled LOB columns with some amount of data and
3) Deleted the LOB-data with empty() function
4) Then obtain the space occupied by LOB-segments and
5) Try commands to free occupied LOB-space:
- ALTER TABLE ... MODIFY LOB(COLUMN_NAME) (DEALLOCATE UNUSED [keep 0]);
- ALTER TABLE ... MODIFY LOB(COLUMN_NAME) (SHRINK SPACE CASCADE);
The script below:
--------------------------------------
set lines 200
set pages 99
conn / as sysdba
drop user test_lob cascade;
drop tablespace test_lob including contents and datafiles;
create tablespace test_lob extent management local segment space management auto datafile '/oradata/ORCL/test_lob.dbf' size 100m autoextend off;
create user test_lob identified by test_lob default tablespace test_lob;
grant dba to test_lob;
conn test_lob/test_lob
create table lob_deallocate
( va varchar2(100 BYTE)
,BASICFILE_BLOB BLOB
,BASICFILE_CLOB CLOB
,SECUREFILE_BLOB BLOB
,SECUREFILE_CLOB CLOB
)
TABLESPACE test_lob
LOB (BASICFILE_BLOB) STORE AS BASICFILE BF_BLOB (TABLESPACE TEST_LOB DISABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING )
LOB (BASICFILE_CLOB) STORE AS BASICFILE BF_CLOB (TABLESPACE TEST_LOB DISABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING )
LOB (SECUREFILE_BLOB) STORE AS SECUREFILE SF_BLOB (TABLESPACE TEST_LOB DISABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING )
LOB (SECUREFILE_CLOB) STORE AS SECUREFILE SF_CLOB (TABLESPACE TEST_LOB DISABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING ) ;
-- Fill the table with some data to blow up LOB-segments
declare
l_data long := rpad('*',30000,'*');
begin
for i in 1 .. 100
loop
insert into lob_deallocate values ( i,
utl_raw.cast_to_raw(l_data),
utl_raw.cast_to_raw(l_data),
utl_raw.cast_to_raw(l_data),
utl_raw.cast_to_raw(l_data) );
end loop;
end;
/
commit;
-- Measure the LOB-segments
select sum(dbms_lob.getlength(BASICFILE_BLOB))
,sum(dbms_lob.getlength(BASICFILE_CLOB))
,sum(dbms_lob.getlength(SECUREFILE_BLOB))
,sum(dbms_lob.getlength(SECUREFILE_CLOB))
from lob_deallocate;
col tablespace_name for a6
col segment_name for a28
select SEGMENT_NAME,SEGMENT_TYPE,SEGMENT_SUBTYPE, BYTES, BLOCKS,EXTENTS, INITIAL_EXTENT,NEXT_EXTENT from user_segments;
select SEGMENT_NAME,SEGMENT_SUBTYPE,BYTES,BLOCKS,EXTENTS,INITIAL_EXTENT,NEXT_EXTENT from user_segments where SEGMENT_TYPE='LOBSEGMENT';
select * from USER_LOBS;
-- Delete the LOB-data
update lob_deallocate set
BASICFILE_BLOB=empty_blob(),
BASICFILE_CLOB=empty_clob(),
SECUREFILE_BLOB=empty_blob(),
SECUREFILE_CLOB=empty_clob();
commit;
-- Measure the LOB-segments
select sum(dbms_lob.getlength(BASICFILE_BLOB))
,sum(dbms_lob.getlength(BASICFILE_CLOB))
,sum(dbms_lob.getlength(SECUREFILE_BLOB))
,sum(dbms_lob.getlength(SECUREFILE_CLOB))
from lob_deallocate;
select SEGMENT_NAME,SEGMENT_SUBTYPE,BYTES,BLOCKS,EXTENTS,INITIAL_EXTENT,NEXT_EXTENT from user_segments where SEGMENT_TYPE='LOBSEGMENT';
ALTER TABLE lob_deallocate ENABLE ROW MOVEMENT;
-- try ALTER TABLE ... MODIFY LOB(COLUMN_NAME) (DEALLOCATE UNUSED [keep 0]);
-- try ALTER TABLE ... MODIFY LOB(COLUMN_NAME) (SHRINK SPACE CASCADE);
ALTER TABLE lob_deallocate MODIFY LOB (BASICFILE_BLOB) (DEALLOCATE UNUSED keep 0);
ALTER TABLE lob_deallocate MODIFY LOB (BASICFILE_CLOB) (DEALLOCATE UNUSED keep 0);
ALTER TABLE lob_deallocate MODIFY LOB (SECUREFILE_BLOB) (DEALLOCATE UNUSED keep 0);
ALTER TABLE lob_deallocate MODIFY LOB (SECUREFILE_CLOB) (DEALLOCATE UNUSED keep 0);
-- Measure the LOB-segments
select SEGMENT_NAME,SEGMENT_SUBTYPE,BYTES,BLOCKS,EXTENTS,INITIAL_EXTENT,NEXT_EXTENT from user_segments where SEGMENT_TYPE='LOBSEGMENT';
------------------------------------------
Tablespace TEST_LOB created.
User TEST_LOB created.
Grant succeeded.
Connected.
Table LOB_DEALLOCATE created.
PL/SQL procedure successfully completed.
Commit complete.
SUM(DBMS_LOB.GETLENGTH(BASICFILE_BLOB)) SUM(DBMS_LOB.GETLENGTH(BASICFILE_CLOB)) SUM(DBMS_LOB.GETLENGTH(SECUREFILE_BLOB)) SUM(DBMS_LOB.GETLENGTH(SECUREFILE_CLOB))
_______________________________________ _______________________________________ ________________________________________ ________________________________________
3000000 3276700 3000000 3276700
SEGMENT_NAME SEGMENT_TYPE SEGMENT_SUBTYPE BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT
___________________________ _______________ __________________ ___________ _________ __________ _________________ ______________
BF_BLOB LOBSEGMENT ASSM 4194304 512 19 65536 1048576
BF_CLOB LOBSEGMENT ASSM 8388608 1024 23 65536 1048576
LOB_DEALLOCATE TABLE ASSM 65536 8 1 65536 1048576
SF_BLOB LOBSEGMENT SECUREFILE 5439488 664 7 106496 1048576
SF_CLOB LOBSEGMENT SECUREFILE 16973824 2072 11 106496 1048576
SYS_IL0000077702C00002$$ LOBINDEX ASSM 65536 8 1 65536 1048576
SYS_IL0000077702C00003$$ LOBINDEX ASSM 65536 8 1 65536 1048576
SYS_IL0000077702C00004$$ LOBINDEX ASSM 65536 8 1 65536 1048576
SYS_IL0000077702C00005$$ LOBINDEX ASSM 65536 8 1 65536 1048576
9 rows selected.
SEGMENT_NAME SEGMENT_SUBTYPE BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT
_______________ __________________ ___________ _________ __________ _________________ ______________
BF_BLOB ASSM 4194304 512 19 65536 1048576
BF_CLOB ASSM 8388608 1024 23 65536 1048576
SF_BLOB SECUREFILE 5439488 664 7 106496 1048576
SF_CLOB SECUREFILE 16973824 2072 11 106496 1048576
TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME INDEX_NAME CHUNK PCTVERSION RETENTION FREEPOOLS CACHE LOGGING ENCRYPT COMPRESSION DEDUPLICATION IN_ROW FORMAT PARTITIONED SECUREFILE SEGMENT_CREATED RETENTION_TYPE RETENTION_VALUE
_________________ __________________ _______________ __________________ ___________________________ ________ _____________ ____________ ____________ ________ __________ __________ ______________ ________________ _________ __________________ ______________ _____________ __________________ _________________ __________________
LOB_DEALLOCATE BASICFILE_BLOB BF_BLOB TEST_LOB SYS_IL0000077702C00002$$ 8192 900 NO YES NONE NONE NONE NO NOT APPLICABLE NO NO YES YES
LOB_DEALLOCATE BASICFILE_CLOB BF_CLOB TEST_LOB SYS_IL0000077702C00003$$ 8192 900 NO YES NONE NONE NONE NO ENDIAN NEUTRAL NO NO YES YES
LOB_DEALLOCATE SECUREFILE_BLOB SF_BLOB TEST_LOB SYS_IL0000077702C00004$$ 8192 NO YES NO NO NO NO NOT APPLICABLE NO YES YES DEFAULT
LOB_DEALLOCATE SECUREFILE_CLOB SF_CLOB TEST_LOB SYS_IL0000077702C00005$$ 8192 NO YES NO NO NO NO ENDIAN NEUTRAL NO YES YES DEFAULT
100 rows updated.
Commit complete.
SUM(DBMS_LOB.GETLENGTH(BASICFILE_BLOB)) SUM(DBMS_LOB.GETLENGTH(BASICFILE_CLOB)) SUM(DBMS_LOB.GETLENGTH(SECUREFILE_BLOB)) SUM(DBMS_LOB.GETLENGTH(SECUREFILE_CLOB))
__________________________________________ __________________________________________ ___________________________________________ ___________________________________________
0 0 0 0
Before DEALLOCATE UNUSED:
SEGMENT_NAME SEGMENT_SUBTYPE BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT
_______________ __________________ ___________ _________ __________ _________________ ______________
BF_BLOB ASSM 4194304 512 19 65536 1048576
BF_CLOB ASSM 8388608 1024 23 65536 1048576
SF_BLOB SECUREFILE 5439488 664 7 106496 1048576
SF_CLOB SECUREFILE 16973824 2072 11 106496 1048576
Table LOB_DEALLOCATE altered. -- row movement
Table LOB_DEALLOCATE altered.
Table LOB_DEALLOCATE altered.
Table LOB_DEALLOCATE altered.
Table LOB_DEALLOCATE altered.
After DEALLOCATE UNUSED:
SEGMENT_NAME SEGMENT_SUBTYPE BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT
_______________ __________________ ___________ _________ __________ _________________ ______________
BF_BLOB ASSM 4194304 512 19 65536 1048576
BF_CLOB ASSM 8388608 1024 23 65536 1048576
SF_BLOB SECUREFILE 5439488 664 7 106496 1048576
SF_CLOB SECUREFILE 16973824 2072 11 106496 1048576
As we see DEALLOCATE UNUSED doesn''t free the space occupied by LOBs.
------------------------------------------
The 2nd run is for SHRINK SPACE, i changed 4 lines in my script:
ALTER TABLE lob_deallocate MODIFY LOB (BASICFILE_BLOB) (SHRINK SPACE CASCADE);
ALTER TABLE lob_deallocate MODIFY LOB (BASICFILE_CLOB) (SHRINK SPACE CASCADE);
ALTER TABLE lob_deallocate MODIFY LOB (SECUREFILE_BLOB) (SHRINK SPACE CASCADE);
ALTER TABLE lob_deallocate MODIFY LOB (SECUREFILE_CLOB) (SHRINK SPACE CASCADE);
Before SHRINK:
SEGMENT_NAME SEGMENT_SUBTYPE BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT
_______________ __________________ ___________ _________ __________ _________________ ______________
BF_BLOB ASSM 4194304 512 19 65536 1048576
BF_CLOB ASSM 8388608 1024 23 65536 1048576
SF_BLOB SECUREFILE 5373952 656 6 106496 1048576
SF_CLOB SECUREFILE 16908288 2064 10 106496 1048576
Table LOB_DEALLOCATE_UNUSED altered. -- row movement
Table LOB_DEALLOCATE_UNUSED altered.
Table LOB_DEALLOCATE_UNUSED altered.
Table LOB_DEALLOCATE_UNUSED altered.
Table LOB_DEALLOCATE_UNUSED altered.
After SHRINK:
SEGMENT_NAME SEGMENT_SUBTYPE BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT
_______________ __________________ ___________ _________ __________ _________________ ______________
BF_BLOB ASSM 65536 8 1 65536 1048576
BF_CLOB ASSM 65536 8 1 65536 1048576
SF_BLOB SECUREFILE 5373952 656 6 106496 1048576
SF_CLOB SECUREFILE 16908288 2064 10 106496 1048576
-----------------------------------------------------
SHRINK works for BASICFILE only:
| BLOB | CLOB |
-------------------------
BASICFILE | YES | YES |
SECUREFILE| NO | NO |
--------------------------
