4 TB LOB Segment (ANYDATA) - Need suggestions

From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
Date: Wed, 5 Aug 2020 11:56:03 -0400
Message-ID: <CAP79kiR=dktoxyiKuOj5PB8YxUAmB=PvkTqF_dJP=zFVDLpNhQ_at_mail.gmail.com>

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

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

Received on Wed Aug 05 2020 - 17:56:03 CEST