WG: AW: 4 TB LOB Segment (ANYDATA) - Need suggestions
Date: Wed, 5 Aug 2020 18:50:04 +0200 (CEST)
Message-ID: <1596646204020.79947.4435f584176466522ebcc0df295412a49f9c4ac4_at_spica.telekom.de>
Hi,
to see whether you have space wasting, you have to compare the length of the content with the chunk size from the lob definition.
For example if the chunk size = 32k and the size of the value stored in the CLB equal to 5k (less then 4k are inlined) then the difference (27k) is allocated but not used.
Regards
Ahmed Fikri
-----Original-Nachricht-----
Betreff: 4 TB LOB Segment (ANYDATA) - Need suggestions
Datum: 2020-08-05T17:57:11+0200
Von: "Chris Taylor" <christopherdtaylor1994_at_gmail.com>
An: "ORACLE-L" <oracle-l_at_freelists.org>
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
The results above are generated via this query:
select ds.owner, ds.segment_name, ds.segment_type, ds.segment_subtype,
Thanks,
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.
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
/
Chris
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Aug 05 2020 - 18:50:04 CEST