How to Check Empty Space in a Securefile LOB

Hi,

PS: please check last sql statement in this post for shorter and faster solution. first sql is for showing all information about the lob.

Yesterday, while checking segment space usage data in a client’s database and some of the LOB segments were absurdly big. So, I want to check what is in it and how much free space I can gain.

To do that, I cannot use just DBMS_LOB.GetLength function because for CLOB data type, it takes much more space than original data because of charsets (especially if you use AL32UTF8 charset) so I just want to check currently empty space in lob segment. if you need that kind of data then, DBMS_SPACE is the solution. DBMS_SPACE has a SPACE_USAGE procedure which checks a segment and return space information but starting 12c, SECUREFILE is the default lob type and for securefile you cannot use same space_usage procedure with the one you use for tables.

space_usage has overloaded versions and second and third overloaded version (order in dbms_space package from top to bottom) is for SECUREFILE lob segments.

here is a sample script I wrote to check currently empty space in securefile lobs:

Caution: I check all LOBS in the database so, you might not want to do that, just modify sql and add necessary filters.

with function f_lob_space_info(p_segment_owner varchar2, p_segment_name varchar2, p_segment_type varchar2, p_partition_name varchar2) return varchar2 as
     segment_size_blocks number; segment_size_bytes  number; 
     used_blocks number; used_bytes number; 
     expired_blocks number; expired_bytes number; 
     unexpired_blocks number; unexpired_bytes number; 
    begin
      dbms_space.space_usage(p_segment_owner, p_segment_name, p_segment_type,
                             segment_size_blocks, segment_size_bytes,
                             used_blocks, used_bytes,
                             expired_blocks, expired_bytes,
                             unexpired_blocks, unexpired_bytes,
                             p_partition_name);
      return 'segment_size_blocks:'|| segment_size_blocks || ' used_blocks:' || used_blocks || ' expired_blocks:' ||expired_blocks ||' unexpired_blocks:' || unexpired_blocks;
    end;
    get_info as (
      select f_lob_space_info(owner, segment_name, 
                              decode(segment_type, 'LOBSEGMENT', 'LOB', segment_type), 
                              partition_name) as lob_info,
             segment_type, owner, segment_name, partition_name, bytes
      from   dba_segments  
      where segment_subtype='SECUREFILE'
        and segment_type in ('LOBSEGMENT', 'LOB PARTITION') 
    ),
    parse_info as (
      select to_number(regexp_substr(lob_info, 'segment_size_blocks:([0-9]+)',1,1,'i',1)) * t.block_size /1024/1024 as segment_size_mb,
             to_number(regexp_substr(lob_info, 'used_blocks:([0-9]+)',1,1,'i',1))* t.block_size /1024/1024          as used_size_mb,
             to_number(regexp_substr(lob_info, 'expired_blocks:([0-9]+)',1,1,'i',1))* t.block_size /1024/1024       as expired_size_mb,
             g.owner, l.table_name, l.column_name, g.segment_name lob_name
      from get_info g
             join dba_lobs l on g.owner = l.owner and g.segment_name = l.segment_name
               join dba_tablespaces t on t.tablespace_name = l.tablespace_name
    )
select segment_size_mb - used_size_mb empty_size, parse_info.*
from parse_info
order by empty_size desc 
/


EMPTY_SIZE	SEGMENT_SIZE_MB	USED_SIZE_MB	EXPIRED_SIZE_MB	OWNER	TABLE_NAME		COLUMN_NAME		LOB_NAME
90.203125	120.1875	29.984375	89.390625	MDSYS	SDO_COORD_OP_PARAM_VALS	PARAM_VALUE_FILE	SYS_LOB0000063832C00006$$
48.1875		48.1875		0		47.7109375	MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
48.1875		48.1875		0		47.7109375	MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
40.1875		40.1875		0		39.71875	MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
40.1875		40.1875		0		39.71875	MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
40.1875		40.1875		0		39.71875	MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
40.1875		40.1875		0		39.71875	MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
27.7265625	32.125		4.3984375	27.0703125	SYS	WRI$_EMX_FILES		DATA			SYS_LOB0000007424C00004$$
14.640625	24.1875		9.546875	14.015625	SYS	WRI$_EMX_FILES		BINARY_DATA		SYS_LOB0000007424C00005$$
11.1171875	16.1875		5.0703125	10.5		MDSYS	SDO_XSD_TABLE		XSD_DOC			SYS_LOB0000067098C00002$$

with function f_lob_space_info(p_segment_owner varchar2, p_segment_name varchar2, p_segment_type varchar2, p_partition_name varchar2) return varchar2 as

segment_size_blocks number; segment_size_bytes number;

used_blocks number; used_bytes number;

expired_blocks number; expired_bytes number;

unexpired_blocks number; unexpired_bytes number;

begin

dbms_space.space_usage(p_segment_owner, p_segment_name, p_segment_type,

segment_size_blocks, segment_size_bytes,

used_blocks, used_bytes,

expired_blocks, expired_bytes,

unexpired_blocks, unexpired_bytes,

p_partition_name);

return 'segment_size_blocks:'|| segment_size_blocks || ' used_blocks:' || used_blocks || ' expired_blocks:' ||expired_blocks ||' unexpired_blocks:' || unexpired_blocks;

end;

get_info as (

select f_lob_space_info(owner, segment_name,

decode(segment_type, 'LOBSEGMENT', 'LOB', segment_type),

partition_name) as lob_info,

segment_type, owner, segment_name, partition_name, bytes

from dba_segments

where segment_subtype='SECUREFILE'

and segment_type in ('LOBSEGMENT', 'LOB PARTITION')

parse_info as (

select to_number(regexp_substr(lob_info, 'segment_size_blocks:([0-9]+)',1,1,'i',1)) * t.block_size /1024/1024 as segment_size_mb,

to_number(regexp_substr(lob_info, 'used_blocks:([0-9]+)',1,1,'i',1))* t.block_size /1024/1024 as used_size_mb,

to_number(regexp_substr(lob_info, 'expired_blocks:([0-9]+)',1,1,'i',1))* t.block_size /1024/1024 as expired_size_mb,

g.owner, l.table_name, l.column_name, g.segment_name lob_name

from get_info g

join dba_lobs l on g.owner = l.owner and g.segment_name = l.segment_name

join dba_tablespaces t on t.tablespace_name = l.tablespace_name

)

select segment_size_mb - used_size_mb empty_size, parse_info.*

from parse_info

order by empty_size desc

EMPTY_SIZE SEGMENT_SIZE_MB USED_SIZE_MB EXPIRED_SIZE_MB OWNER TABLE_NAME COLUMN_NAME LOB_NAME

90.203125 120.1875 29.984375 89.390625 MDSYS SDO_COORD_OP_PARAM_VALS PARAM_VALUE_FILE SYS_LOB0000063832C00006$$

48.1875 48.1875 0 47.7109375 MUSTAFA TMP DD SYS_LOB0000073457C00085$$

40.1875 40.1875 0 39.71875 MUSTAFA TMP DD SYS_LOB0000073457C00085$$

27.7265625 32.125 4.3984375 27.0703125 SYS WRI$_EMX_FILES DATA SYS_LOB0000007424C00004$$

14.640625 24.1875 9.546875 14.015625 SYS WRI$_EMX_FILES BINARY_DATA SYS_LOB0000007424C00005$$

11.1171875 16.1875 5.0703125 10.5 MDSYS SDO_XSD_TABLE XSD_DOC SYS_LOB0000067098C00002$$

first with function block is to call dbms_space.space_usage procedure and return data in string, on second with clause (get_info) call the function and get lob information, by the way SEGMENT_TYPE for a lob segment is “LOB SEGMENT” but dbms_space.space_usage does not accept it as segment_Type, you must send “LOB” instead, then parse result as columns and list SEGMENT_SIZE_MB which is total size of LOB segment size in megabytes
USED_SIZE_MB which is actual used size in megabytes
EXPIRED_SIZE_MB which is expired data size (for versioning) in megabytes.

to calculate empty space just subtract used size from segment size. this is approximately empty size in your lob segment. if you have a partitioned table then you will get result for each partition (like mustafa.tmp table in the example). I hope this script helps in your job.

EDIT:

I forgot to mentioned why lob segments have empty spaces. biggest reason is delete operation of course. if you delete a big portion of the table (or lob segment) then deleted space will be considered as empty space. So, if you delete on a table, should you shrink the lob segment or table? NO! that space will be used by the table again. if this is your standard operation then empty spaces in table/lob segment will be reused but let’s say you inserted many rows by a mistake or because of a bug in your code and then deleted those rows. if you have enough disk space for the database then there is nothing to worry about (mostly). That table will get many inserts in time and it will use that space eventually but if too much space is reserved in the table / lob segment and maybe you have a disk space issues then you can reclaim it (by shrinking or moving table/lob segment). So, this is not a regular thing.

EDIT 2:

sql above is aiming to show all information about lob like used space, total space, expired space etc. probably , you will just need to see empty space in lob. so you can use this sql statement. this will work faster because no regexp usage and parsing operations. I also subtracted “unexpired bytes” from total size since this space is still in use.

with function f_lob_free_space_info(p_segment_owner varchar2, p_segment_name varchar2, p_segment_type varchar2, p_partition_name varchar2) return varchar2 as
     segment_size_blocks number; segment_size_bytes  number; 
     used_blocks number; used_bytes number; 
     expired_blocks number; expired_bytes number; 
     unexpired_blocks number; unexpired_bytes number; 
    begin
      dbms_space.space_usage(p_segment_owner, p_segment_name, p_segment_type,
                             segment_size_blocks, segment_size_bytes,
                             used_blocks, used_bytes,
                             expired_blocks, expired_bytes,
                             unexpired_blocks, unexpired_bytes,
                             p_partition_name);
      return segment_size_bytes - used_bytes - unexpired_bytes;
    end;
    get_info as (
      select f_lob_free_space_info(s.owner, s.segment_name, 
                                   decode(s.segment_type, 'LOBSEGMENT', 'LOB', s.segment_type), 
                                   s.partition_name)/1024/1024 as potential_empty_space_in_mb,
             s.segment_type, s.owner,
             l.table_name, l.column_name, s.segment_name lob_name, s.partition_name
      from   dba_segments s
               join dba_lobs l on s.owner = l.owner and s.segment_name = l.segment_name
      where s.segment_subtype='SECUREFILE'
        and s.segment_type in ('LOBSEGMENT', 'LOB PARTITION') 
    )
select *
from get_info
order by potential_empty_space_in_mb desc 
/

EMPTY_SPACE_IN_MB	OWNER	TABLE_NAME		COLUMN_NAME		LOB_NAME
90.203125		MDSYS	SDO_COORD_OP_PARAM_VALS	PARAM_VALUE_FILE	SYS_LOB0000063832C00006$$
48.1875			MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
48.1875			MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
40.1875			MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
40.1875			MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
40.1875			MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
40.1875			MUSTAFA	TMP			DD			SYS_LOB0000073457C00085$$
27.7265625		SYS	WRI$_EMX_FILES		DATA			SYS_LOB0000007424C00004$$
14.640625		SYS	WRI$_EMX_FILES		BINARY_DATA		SYS_LOB0000007424C00005$$
11.1171875		MDSYS	SDO_XSD_TABLE		XSD_DOC			SYS_LOB0000067098C00002$$

with function f_lob_free_space_info(p_segment_owner varchar2, p_segment_name varchar2, p_segment_type varchar2, p_partition_name varchar2) return varchar2 as

segment_size_blocks number; segment_size_bytes number;

used_blocks number; used_bytes number;

expired_blocks number; expired_bytes number;

unexpired_blocks number; unexpired_bytes number;

begin

dbms_space.space_usage(p_segment_owner, p_segment_name, p_segment_type,

segment_size_blocks, segment_size_bytes,

used_blocks, used_bytes,

expired_blocks, expired_bytes,

unexpired_blocks, unexpired_bytes,

p_partition_name);

return segment_size_bytes - used_bytes - unexpired_bytes;

end;

get_info as (

select f_lob_free_space_info(s.owner, s.segment_name,

decode(s.segment_type, 'LOBSEGMENT', 'LOB', s.segment_type),

s.partition_name)/1024/1024 as potential_empty_space_in_mb,

s.segment_type, s.owner,

l.table_name, l.column_name, s.segment_name lob_name, s.partition_name

from dba_segments s

join dba_lobs l on s.owner = l.owner and s.segment_name = l.segment_name

where s.segment_subtype='SECUREFILE'

and s.segment_type in ('LOBSEGMENT', 'LOB PARTITION')

)

select *

from get_info

order by potential_empty_space_in_mb desc

EMPTY_SPACE_IN_MB OWNER TABLE_NAME COLUMN_NAME LOB_NAME

90.203125 MDSYS SDO_COORD_OP_PARAM_VALS PARAM_VALUE_FILE SYS_LOB0000063832C00006$$

48.1875 MUSTAFA TMP DD SYS_LOB0000073457C00085$$

40.1875 MUSTAFA TMP DD SYS_LOB0000073457C00085$$

27.7265625 SYS WRI$_EMX_FILES DATA SYS_LOB0000007424C00004$$

14.640625 SYS WRI$_EMX_FILES BINARY_DATA SYS_LOB0000007424C00005$$

11.1171875 MDSYS SDO_XSD_TABLE XSD_DOC SYS_LOB0000067098C00002$$

thanks for reading, your comments are always welcome. wish you healthy days.

Leave a Reply Cancel reply