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
