Released space from SECUREFILE/BASICFILE LOBS segments

Released space from SECUREFILE/BASICFILE LOBS segments

We need to released the space to OS from securefile / Basicfile Lobs object used in Oracle.
Alter table space shrink is not worked on Lobs object for released the space.
We need to use move command to released space back to Oracle.

Check its Securefile/ Basicfile lobs
–If SECUREFILE column in ALL_LOBS is YES,then the LOB is SECUREFILE. If it is NO,then the LOB is BASICFILE

Select OWNER, TABLE_NAME, COLUMN_NAME, TABLESPACE_NAME, SECUREFILE from ALL_LOBS where SEGMENT_NAME in ('SYS_LOB0000006339C00038$$');
We need to use the SECUREFILE /BASICFILE clause in ALTER TABLE ... MOVE LOB statement appropriately.
ALTER TABLE MOVE LOB() STORE AS [SECUREFILE/BASICFILE]
(TABLESPACE or );

Following are the example show you how to released space from LOBs Segments:

1. CREATE USER, TABLESPACE AND TABLE WITH LOB COLUMN.

CREATE TABLESPACE TEST_TB DATAFILE 'C:\ORACLE\ORADATA\XE\PDB2\TEST_TB01.DBF' SIZE 100M;

create user test identified by test;
grant dba to test;
alter user test default tablespace test_tb;
connect test/test;

CREATE TABLE test ( ID NUMBER, V_DATA VARCHAR2(4000), PHOTO BLOB) LOB (PHOTO) STORE AS SECUREFILE ;
OR
CREATE TABLE test ( ID NUMBER, V_DATA VARCHAR2(4000), PHOTO BLOB) LOB (PHOTO) STORE AS BASICFILE;

CREATE TABLE test_bfile ( B_FILE BFILE) ;

CREATE or REPLACE DIRECTORY test as 'C:\test';

2. INSERT DATA INTO THE TABLES AND LOB SEGMENTS:

-- INSERT THE BFILE LOCATOR FOR THE PHOTO

insert into test_bfile values ( bfilename('TEST','1.PNG'));

commit;

-- INSERT 50 COPIES OF THE PHOTO INTO THE TEST TABLE

declare
tmp_blob blob default EMPTY_BLOB();
tmp_bfile bfile:=null;
tmp_varchar varchar2(4000):=null;
dest_offset integer:=1;
src_offset integer:=1;
begin
for i in 1..4000 loop
TMP_VARCHAR := TMP_VARCHAR||'A';
end loop;
select b_file into tmp_bfile from test_bfile;
DBMS_LOB.OPEN (tmp_bfile, DBMS_LOB.FILE_READONLY);
dbms_lob.createtemporary(tmp_blob, TRUE);
DBMS_LOB.LOADBLOBFROMFILE(tmp_blob,tmp_bfile,DBMS_LOB.LOBMAXSIZE,dest_offset,src_offset);
for i in 1..50 loop
insert into test values(i,tmp_varchar, tmp_blob);
commit;
end loop;
DBMS_LOB.CLOSE(tmp_bfile);
end;
/

3. CHECK THE STORAGE UTILIZED BY THE USER.

column segment_name format a30
column tablespace_name format a13
set pagesize 1000
select segment_name, sum(bytes) BYTES, TABLESPACE_NAME, count(*) EXTENTS
from user_extents
group by segment_name, TABLESPACE_NAME
order by 1;

SEGMENT_NAME                        BYTES TABLESPACE_NA    EXTENTS
------------------------------ ---------- ------------- ----------
SYS_IL0000073774C00003$$            65536 TEST_TB                1
SYS_LOB0000073774C00003$$         8519680 TEST_TB                9
TEST                               458752 TEST_TB                7
TEST_BFILE                          65536 TEST_TB                1

4. DELETE ROWS FROM THE TEST TABLE

SQL> delete from test where (id/2) = trunc(id/2);
25 rows deleted.

SQL> commit;
Commit complete.

5. SHOW THAT NO CHANGE HAS OCCURRED WITH THE STORAGE FOR THE TABLE

select segment_name, sum(bytes) BYTES, TABLESPACE_NAME, count(*) EXTENTS
from user_extents
group by segment_name, TABLESPACE_NAME
order by 1;

SEGMENT_NAME                        BYTES TABLESPACE_NA    EXTENTS
------------------------------ ---------- ------------- ----------
SYS_IL0000073774C00003$$            65536 TEST_TB                1
SYS_LOB0000073774C00003$$         8519680 TEST_TB                9
TEST                               458752 TEST_TB                7
TEST_BFILE                          65536 TEST_TB                1

6. MOVE THE TABLE FROM TABLESPACE AND EXAMINE THE STORAGE

-- Reorg the table
ALTER TABLE TEST MOVE TABLESPACE TEST_tb;

-- Reorg the securefile LOB
ALTER TABLE TEST MOVE LOB(PHOTO) STORE AS SECUREFILE (TABLESPACE TEST_TB);
OR
-- Reorg from basicfile LOB
ALTER TABLE TEST MOVE LOB(PHOTO) STORE AS BASICFILE (TABLESPACE TEST_AUTO);


7. EXAMINE THE STORAGE AFTER THE MOVE

select segment_name, sum(bytes) BYTES, TABLESPACE_NAME, count(*) EXTENTS
from user_extents
group by segment_name, TABLESPACE_NAME
order by 1;

SEGMENT_NAME                        BYTES TABLESPACE_NA    EXTENTS
------------------------------ ---------- ------------- ----------
SYS_IL0000073774C00003$$            65536 TEST_TB                1
SYS_LOB0000073774C00003$$         4325376 TEST_TB                5
TEST                               262144 TEST_TB                4
TEST_BFILE                          65536 TEST_TB                1


Note: It will clear allocated space from LOB objects and table.

8. Clean the example data

CONNECT / AS SYSDBA;

DROP USER TEST CASCADE;
DROP TABLESPACE TEST_AUTO INCLUDING CONTENTS AND DATAFILES;

Note:
1. After Moving a table changes the rowids, So statistics become invalid we need to gather again
EXEC DBMS_STATS.GATHER_TABLE_STATS ('owner', 'table')

2 thoughts on “Released space from SECUREFILE/BASICFILE LOBS segments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.