RE: [EXTERNAL] Re: 4 TB LOB Segment (ANYDATA) - Need suggestions
Date: Thu, 6 Aug 2020 03:59:17 +0000
Message-ID: <BLAPR19MB4354C439C50ED970D971C916BB480_at_BLAPR19MB4354.namprd19.prod.outlook.com>
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:
Thanks.
-Saad
Working remote.
Reachable via email, Teams & (631)796-5320.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mladen Gogala
Sent: Wednesday, August 05, 2020 10:43 PM
To: oracle-l_at_freelists.org
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.
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Aug 06 2020 - 05:59:17 CEST