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:
https://nguyentichthanh.wordpress.com/how-to-shrink-make-less-sparse-a-lob-basicfile-or-securefile-doc-id-1451124-1/
Thanks.
-Saad
Working remote.
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
To: oracle-l@xxxxxxxxxxxxx
Subject: [EXTERNAL] Re: 4 TB LOB Segment (ANYDATA) - Need suggestions
External Email. Use Caution.
Hi Chris,
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.
Regards
On 8/5/20 11:56 AM, Chris Taylor wrote:
Env: 12.1.0.2 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.
OWNER
SEGMENT_NAME
SEGMENT_TYPE
SEGMENT_SUBTYPE
TABLESPACE_NAME
SIZE_GB
METADATA
SYS_LOB0040419200C00016$$
LOBSEGMENT
SECUREFILE
METADATA
4679.496277
TABLE_NAME
COLUMN_NAME
INDEX_NAME
PCTVERSION
RETENTION
FREEPOOLS
CACHE
LOGGING
ENCRYPT
COMPRESSION
DEDUPLICATION
IN_ROW
FORMAT
PARTITIONED
SECUREFILE
RETENTION_TYPE
RETENTION_VALUE
VALUE_LIST
SYS_NC00016$
SYS_IL0040419200C00016$$
YES
YES
NO
NO
NO
YES
NOT APPLICABLE
NO
YES
DEFAULT
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.cache, ls.logging,
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
/
Thanks,
Chris
--
Mladen Gogala
Database Consultant
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.
