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')
Smart way of Technology 
Explanation is very good thanks
LikeLike
nice Explanation
LikeLiked by 1 person