In this Document

APPLIES TO:

Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.

SYMPTOMS

LOB space Not Getting released

Note: be aware that the unused space after deletion can be reused in the LOB segment for further insert after retention time is passed.

CHANGES

CAUSE

LOB space not getting released after Delete from TABLE commands when using Securefile / Basic File.

Check If the Application code is using any of the Delete Commands if so you can check this.

SOLUTION

This solution is applicable for columns with Basicfile / Securefile LOB storage.

You can check other options like Shrinking LOBS / How to Shrink a Securefile LOB Using Online Redefinition (DBMS_REDEFINITION)? (Doc ID 1394613.1)

This steps is to confirm that the Truncate only reclaim the space from Object , Block , Segment , Tablespace Level.

A) Tested in 12.2 ::

1) Create Database Objects in 12.2 to perform test case:

[test]/etc> sqlplus
SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 10 17:28:20 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select name,created from v$database;
NAME        CREATED
--------- -----------
XXX        10-JUL-17
SQL> CREATE SMALLFILE TABLESPACE "TTS_NAME" DATAFILE '/refresh/home/app/oracle/oradata/xxx/DB01.dbf' SIZE 3072M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE 3072M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
SQL> create table lob_test (id number, data blob) tablespace "TTS_NAME";
Table created.
SQL> create sequence lob_test_seq;
Sequence created.
SQL> Create directory TEST as '/tmp/test';
Directory created.
SQL> grant read,write on directory TEST to sys;
Grant succeeded.
-- For this Test case you can use any Binary file ( dump ,zip file etc and rename to *.dat )
SQL> !ls -ltr *.dat
-rw-r--r-- 1 grdbms grdbms 4759552 Jul 10 16:21 test.dat
       
       

2) To check from Block Level free space create a Function:

SQL> create or replace function get_block_info
( p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default 'TABLE',
p_partition in varchar2 default NULL )
return number
authid current_user
as
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
begin
dbms_space.unused_space
2 ( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
3 partition_name => p_partition,
4 total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
5 unused_bytes => l_unused_bytes,
6 LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
7 LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
8 LAST_USED_BLOCK => l_LAST_USED_BLOCK );
9
10 11 return l_total_blocks || '.' || (l_total_blocks-l_unused_blocks);
exception
12 when others then return NULL;
end;
/
Function created.
 

3) Initially Check for Tablespace Space , Segment and block level Free space :

SQL> col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;
Tablespace                Used MB    Free MB    Total MB   Pct. Free
---------------------- ----------- ----------- ----------- ----------
TTS_NAME              264       2,808       3,072       91                ---->>  New Tablespace more than 90% free
 
-- After creating the Function use this Query to get from Block level
SQL> select table_name,
trunc(gbi) total_blocks,
to_number(substr(gbi,instr(gbi,'.')+1)) used_blocks
from ( select table_name, get_block_info( table_name ) gbi, rownum
from user_tables where Table_name='LOB_TEST'
);
TABLE_NAME             TOTAL_BLOCKS        USED_BLOCKS
-------------       -----------------    -------------------
LOB_TEST                  8                     3                               ---->>  Free Blocks Available            
 

4) Start the sqlplus session from the *.dat file location

 
SQL> DECLARE
src_file BFILE := bfilename('TEST', 'test.dat');
dst_file BLOB;
lgh_file BINARY_INTEGER;
cur_id NUMBER(10);
BEGIN
FOR i IN 1..500                                                                                        ---->> Insert 1 to 500
LOOP
INSERT INTO lob_test(id,data) VALUES(lob_test_seq.nextval,empty_blob())
RETURNING id into cur_id;
-- lock record
SELECT data INTO dst_file FROM lob_test WHERE id=cur_id FOR UPDATE;
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
lgh_file := dbms_lob.getlength(src_file);
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
dbms_lob.fileclose(src_file);
END LOOP;
END;
/
PL/SQL procedure successfully completed.
SQL> select sum(dbms_lob.getchunksize(data))/1024/1024 from lob_test;
SUM(DBMS_LOB.GETCHUNKSIZE(DATA))/1024/1024
------------------------------------------------------------
4.22763824
SQL> SELECT owner||'.'||SEGMENT_NAME NAME, SEGMENT_TYPE TYPE,
EXTENTS, MAX_EXTENTS, MAX_EXTENTS - EXTENTS LEFT_EXTENTS,
NEXT_EXTENT/1024 next_kb, bytes/1024 bytes_kb
FROM DBA_SEGMENTS
WHERE tablespace_name = 'TTS_NAME' ;
NAME                             TYPE              EXTENTS      MAX_EXTENTS   LEFT_EXTENTS  NEXT_KB   BYTES_KB
-----------------------        --------------- --------------- ------------  -------------  -------- ---------- 
SYS.LOB_TEST                    TABLE               1          2147483645     2147483644      1024        64
SYS.SYS_IL0000073541C00002$$    LOBINDEX            1          2147483645     2147483644      1024        64
SYS.SYS_LOB0000073541C00002$$   LOBSEGMENT          212        2147483645     2147483433      1024        2752704
SQL> DECLARE
src_file BFILE := bfilename('TEST', 'test.dat');
dst_file BLOB;
lgh_file BINARY_INTEGER;
cur_id NUMBER(10);
BEGIN
FOR i IN 1..500
LOOP
INSERT INTO lob_test(id,data) VALUES(lob_test_seq.nextval,empty_blob())
RETURNING id into cur_id;
-- lock record
SELECT data INTO dst_file FROM lob_test WHERE id=cur_id FOR UPDATE;
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
lgh_file := dbms_lob.getlength(src_file);
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
dbms_lob.fileclose(src_file);
END LOOP;
END;
/
DECLARE
*
ERROR at line 1:
ORA-01691: unable to extend lob segment SYS.SYS_LOB0000073541C00002$$ by 1024 in tablespace TTS_NAME ---->> Errored out
ORA-06512: at "SYS.DBMS_LOB", line 945
ORA-06512: at line 16
SQL> select table_name,
trunc(gbi) total_blocks,
to_number(substr(gbi,instr(gbi,'.')+1)) used_blocks
from ( select table_name, get_block_info( table_name ) gbi, rownum
from user_tables where Table_name='LOB_TEST'
);
TABLE_NAME        TOTAL_BLOCKS     USED_BLOCKS
-------------------------------  --------------------
LOB_TEST                8                8                                          ---->> Fully Occupied
Tablespace              Used MB    Free MB   Total MB  Pct. Free
---------------------- ----------- -------- ---------  --------------
TTS_NAME          3,070         2      3,072         0                       ---->> Errored out because of no free space ( as expected )
 
SQL> SELECT owner||'.'||SEGMENT_NAME NAME, SEGMENT_TYPE TYPE,
EXTENTS, MAX_EXTENTS, MAX_EXTENTS - EXTENTS LEFT_EXTENTS,
NEXT_EXTENT/1024 next_kb, bytes/1024 bytes_kb
FROM DBA_SEGMENTS
WHERE tablespace_name = 'TTS_NAME' ;
NAME                                              TYPE            EXTENTS   MAX_EXTENTS   LEFT_EXTENTS NEXT_KB BYTES_KB
------------------------------                 ------------------ ---------- ----------- ------------ -----------------------
SYS.LOB_TEST                                       TABLE            1         2147483645   2147483644     1024    64
SYS.SYS_IL0000073541C00002$$                       LOBINDEX         1         2147483645   2147483644     1024    64
SYS.SYS_LOB0000073541C00002$$                      LOBSEGMENT       230       2147483645   2147483415     1024    3143872
 

5) Now after the Space Error Use Delete , but Delete Will not reclaim any space from Object / Block level :

SQL> delete from lob_test;                         
550 rows deleted.
SQL> select table_name,
trunc(gbi) total_blocks,
to_number(substr(gbi,instr(gbi,'.')+1)) used_blocks
from ( select table_name, get_block_info( table_name ) gbi, rownum
from user_tables where Table_name='LOB_TEST'
);
TABLE_NAME             TOTAL_BLOCKS       USED_BLOCKS
-------------     ------------------    ---------------------
LOB_TEST                       8               8                           ---->> Delete will not reclaim Space
 
Tablespace                Used MB    Free MB     Total MB   Pct. Free
---------------------- ----------- ----------- ----------- ----------
TTS_NAME             3,070       2         3,072         0           ---->> Actual issue is "Delete from table not working"
 
SQL> SELECT owner||'.'||SEGMENT_NAME NAME, SEGMENT_TYPE TYPE,
EXTENTS, MAX_EXTENTS, MAX_EXTENTS - EXTENTS LEFT_EXTENTS,
NEXT_EXTENT/1024 next_kb, bytes/1024 bytes_kb
FROM DBA_SEGMENTS
WHERE tablespace_name = 'TTS_NAME' ; 
NAME                           TYPE                EXTENTS   MAX_EXTENTS LEFT_EXTENTS NEXT_KB    BYTES_KB
------------------------------ ------------------ ---------- ----------- ------------ ---------- ----------
SYS.LOB_TEST                   TABLE                 1         2147483645 2147483644    1024        64
SYS.SYS_IL0000073541C00002$$   LOBINDEX              1         2147483645 2147483644    1024        64
SYS.SYS_LOB0000073541C00002$$  LOBSEGMENT            230       2147483645 2147483415    1024        3143872
 

6) Truncate Will Reclaim the Space for Lob ( for both Basic file and Secure file )

SQL> truncate table lob_test; 
Table truncated.
SQL> select table_name,
trunc(gbi) total_blocks,
to_number(substr(gbi,instr(gbi,'.')+1)) used_blocks
from ( select table_name, get_block_info( table_name ) gbi, rownum
from user_tables where Table_name='LOB_TEST'
) ;
TABLE_NAME            TOTAL_BLOCKS      USED_BLOCKS
--------------------------------------------------------------
LOB_TEST                    8            3
SQL>
Tablespace              Used MB     Free MB    Total MB    Pct. Free
---------------------- ----------- ----------- ----------- ----------
TTS_NAME             0         3,072       3,072         100               ---->> Reclaimed after truncate.
 
SQL> SELECT owner||'.'||SEGMENT_NAME NAME, SEGMENT_TYPE TYPE,
EXTENTS, MAX_EXTENTS, MAX_EXTENTS - EXTENTS LEFT_EXTENTS,
NEXT_EXTENT/1024 next_kb, bytes/1024 bytes_kb
FROM DBA_SEGMENTS
WHERE tablespace_name = 'TTS_NAME' ; 2 3 4 5
NAME TYPE EXTENTS MAX_EXTENTS LEFT_EXTENTS NEXT_KB BYTES_KB
------------------------------ ------------------ ---------- 
SYS.LOB_TEST TABLE 1 2147483645 2147483644 1024 64
SYS.SYS_IL0000073541C00002$$ LOBINDEX 1 2147483645 2147483644 1024 64
SYS.SYS_LOB0000073541C00002$$ LOBSEGMENT 1 2147483645 2147483644 1024 128
 

-- Tested in 11g Same Result::

 
[test]/tmp> sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 10 18:26:21 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> CREATE SMALLFILE TABLESPACE "TTS_NAME" DATAFILE '/grdbms/64bit/app/oracle/oradata/xxx/DB01.dbf'
SIZE 3072M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE 3072M
LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; 2 3
Tablespace created.
SQL> create table lob_test (id number, data blob) tablespace "TTS_NAME";
Table created.
SQL> create sequence lob_test_seq;
Sequence created.
SQL> Create directory TEST as '/tmp';
Directory created.
SQL> grant read,write on directory TEST to sys;
Grant succeeded.
SQL> Create Funcation as in 12c in above stesp
SQL> 
Tablespace             Used MB      Free MB    Total MB    Pct. Free
---------------------- ----------- ----------- ----------- ----------

TTS_NAME            0         3,072        3,072        100
 
SQL> DECLARE
src_file BFILE := bfilename('TEST', 'test.dat');
dst_file BLOB;
lgh_file BINARY_INTEGER;
cur_id NUMBER(10);
BEGIN
FOR i IN 1..500                                                                        ----->>> Insert 1 to 500
LOOP
INSERT INTO lob_test(id,data) VALUES(lob_test_seq.nextval,empty_blob())
RETURNING id into cur_id;
-- lock record
SELECT data INTO dst_file FROM lob_test WHERE id=cur_id FOR UPDATE;
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
lgh_file := dbms_lob.getlength(src_file);
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
dbms_lob.fileclose(src_file);
END LOOP;
END;
/
PL/SQL procedure successfully completed.
SQL> select sum(dbms_lob.getchunksize(data))/1024/1024 from lob_test;
SUM(DBMS_LOB.GETCHUNKSIZE(DATA))/1024/1024
------------------------------------------
3.87763977
SQL> SELECT owner||'.'||SEGMENT_NAME NAME, SEGMENT_TYPE TYPE,
EXTENTS, MAX_EXTENTS, MAX_EXTENTS - EXTENTS LEFT_EXTENTS,
NEXT_EXTENT/1024 next_kb, bytes/1024 bytes_kb
FROM DBA_SEGMENTS
WHERE tablespace_name = 'TTS_NAME' ; 2 3 4 5
NAME
--------------------------------------------------------------------------------
TYPE EXTENTS MAX_EXTENTS LEFT_EXTENTS NEXT_KB BYTES_KB
------------------ ---------- ----------- ------------ ---------- ----------
SYS.LOB_TEST
TABLE 2 2147483645 2147483643 1024 128
SYS.SYS_IL0000110586C00002$$
LOBINDEX 17 2147483645 2147483628 1024 2048
SYS.SYS_LOB0000110586C00002$$
LOBSEGMENT 219 2147483645 2147483426 1024 2359296
SQL> DECLARE
src_file BFILE := bfilename('TEST', 'test.dat');
dst_file BLOB;
lgh_file BINARY_INTEGER;
cur_id NUMBER(10);
BEGIN
FOR i IN 1..500
LOOP
INSERT INTO lob_test(id,data) VALUES(lob_test_seq.nextval,empty_blob())
RETURNING id into cur_id;
-- lock record
SELECT data INTO dst_file FROM lob_test WHERE id=cur_id FOR UPDATE;
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
lgh_file := dbms_lob.getlength(src_file);
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
dbms_lob.fileclose(src_file);
END LOOP;
END; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
20 /
DECLARE
*
ERROR at line 1:
ORA-01691: unable to extend lob segment SYS.SYS_LOB0000110586C00002$$ by 8192
in tablespace TTS_NAME
ORA-06512: at "SYS.DBMS_LOB", line 928
ORA-06512: at line 16
Tablespace Used MB Free MB Total MB Pct. Free
---------------------- ----------- ----------- ----------- ----------
SYSAUX 494 26 520 5
UNDOTBS1 51 59 110 54
USERS 18,325 949 19,274 5
SYSTEM 756 14 770 2
TTS_NAME 3,011 61 3,072 2
SQL> select sum(dbms_lob.getchunksize(data))/1024/1024 from lob_test;
SUM(DBMS_LOB.GETCHUNKSIZE(DATA))/1024/1024
------------------------------------------
3.87763977
 
SQL> select table_name,
trunc(gbi) total_blocks,
to_number(substr(gbi,instr(gbi,'.')+1)) used_blocks
from ( select table_name, get_block_info( table_name ) gbi, rownum
from user_tables where Table_name='LOB_TEST');
TABLE_NAME TOTAL_BLOCKS USED_BLOCKS
------------------------------ ------------ -----------
LOB_TEST             16                         16
SQL> delete from lob_test;
500 rows deleted.
SQL> select table_name,
trunc(gbi) total_blocks,
to_number(substr(gbi,instr(gbi,'.')+1)) used_blocks
from ( select table_name, get_block_info( table_name ) gbi, rownum
from user_tables where Table_name='LOB_TEST');
TABLE_NAME               TOTAL_BLOCKS USED_BLOCKS
----------------------- ------------ -----------
LOB_TEST                     16           16
SQL> DECLARE
src_file BFILE := bfilename('TEST', 'test.dat');
dst_file BLOB;
lgh_file BINARY_INTEGER;
cur_id NUMBER(10);
BEGIN
FOR i IN 1..500
LOOP
INSERT INTO lob_test(id,data) VALUES(lob_test_seq.nextval,empty_blob())
RETURNING id into cur_id;
-- lock record
SELECT data INTO dst_file FROM lob_test WHERE id=cur_id FOR UPDATE;
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
lgh_file := dbms_lob.getlength(src_file);
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
dbms_lob.fileclose(src_file);
END LOOP;
END; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
20 /
DECLARE
*
ERROR at line 1:
ORA-01691: unable to extend lob segment SYS.SYS_LOB0000110586C00002$$ by 8192
in tablespace TTS_NAME
ORA-06512: at "SYS.DBMS_LOB", line 928
ORA-06512: at line 16
SQL> truncate table lob_test;
Table truncated.
SQL> select table_name,
trunc(gbi) total_blocks,
to_number(substr(gbi,instr(gbi,'.')+1)) used_blocks
from ( select table_name, get_block_info( table_name ) gbi, rownum
from user_tables where Table_name='LOB_TEST'); 2 3 4 5
TABLE_NAME           TOTAL_BLOCKS    USED_BLOCKS
----------------------------------- ------------ 
LOB_TEST                 8              3
 

NOTE:

We have many options to reclaim the space for LOB Segment . But we need to identify the Application code about the Command what they are using exactly. If Code uses Delete Command then DB level we cannot see any improvement for Block or Segment level free space.

So check what is the command used by Application code and confirm with this Steps.

Refer:

How to Shrink a Securefile LOB Using Online Redefinition (DBMS_REDEFINITION)? (Doc ID 1394613.1)
How To Reclaim Wasted Space on The Segment (Table, Index and LOB) and Tablespace Levels (Doc ID 1682748.1)
Why is no space released after an ALTER TABLE ... SHRINK? (Doc ID 820043.1)
Primary Note:Overview of Online Redefinition of Tables (DBMS_REDEFINITION) (Doc ID 1357825.1)