Re: 4 TB LOB Segment (ANYDATA) - Need suggestions

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


--
http://www.freelists.org/webpage/oracle-l