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$$
