LOB segment fragmentation removal has limited options for sure but that also is
determined by the factor whether the segment itself is BASICFILE or SECUREFILE.
I’ll suggest exhaust all the available metalink notes and see which one suits
you most. Check out this link:
Reachable via email, Teams & (631)796-5320.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Mladen Gogala
Sent: Wednesday, August 05, 2020 10:43 PM
Subject: [EXTERNAL] Re: 4 TB LOB Segment (ANYDATA) - Need suggestions
External Email. Use Caution.
LOB segment is a sort of in-database file system but without anything like
compact. Space wastage is to be expected. Once upon a time, in a now defunct
company called VMS, I tested a successful design with MongoDB. The problem was
how to create text index which existed on the LOB field in the Oracle DB. The
company tested Lucene and Sphinx and opted for the latter. However, that
requires a lot of work and a lot of testing. Wasting space in LOB is horrible,
which is to be expected.
On 8/5/20 11:56 AM, Chris Taylor wrote:
Env: 220.127.116.11 Linux x86-64
So I have the following LOB segment that has reached 4 TB and I'm not sure what
to do about it if anything. (I mainly doubt it really needs 4TB of space and
that we're wasting space here)
The COLUMN on the table is an XMLTYPE datatype called SNAPSHOT
So I need thoughts/suggestions on how to analyze this guy and determine if its
wasting space and if so, what are my options?
I've not really worked with LOBs in depth so I'm a bit out of my depth here.
Any thoughts/comments/suggestions are welcome.
I pasted the values above in table format, but if you'd like to see it in some
other format, let me know.
The results above are generated via this query:
select ds.owner, ds.segment_name, ds.segment_type, ds.segment_subtype,
ds.tablespace_name, ds.bytes/1024/1024/1024 as size_gb, ls.table_name,
ls.column_name, ls.index_name, ls.pctversion, ls.retention, ls.freepools,
ls.encrypt, ls.compression, ls.deduplication, ls.in_row, ls.format,
ls.partitioned, ls.securefile, ls.retention_type, ls.retention_value
from dba_segments ds, dba_lobs ls
where ds.owner = 'METADATA'
and ds.segment_type = 'LOBSEGMENT'
and ds.segment_name = ls.SEGMENT_NAME
and ds.owner = ls.OWNER
and ls.table_name = 'VALUE_LIST'
order by ds.bytes desc
Tel: (347) 321-1217
The information contained in this electronic e-mail transmission and any
attachments are intended only for the use of the individual or entity to whom
or to which it is addressed, and may contain information that is privileged,
confidential and exempt from disclosure under applicable law. If the reader of
this communication is not the intended recipient, or the employee or agent
responsible for delivering this communication to the intended recipient, you
are hereby notified that any dissemination, distribution, copying or disclosure
of this communication and any attachment is strictly prohibited. If you have
received this transmission in error, please notify the sender immediately by
telephone and electronic mail, and delete the original communication and any
attachment from any computer, server or other electronic recording or storage
device or medium. Receipt by anyone other than the intended recipient is not a
waiver of any attorney-client, physician-patient or other privilege.