LOB Segment Oracle Query

Published by Patrizia Gardis on 5 March 20195 March 2019

With the article “LOB Segment Oracle Query,” we provide a query that allows you to see the size of the LOB segment of a table.

As explained in the article Query Oracle Table LOB Query, LOBs are large objects that are divided into CLOB and BLOB.

These objects can reach enormous sizes if not managed with the correct partitioned architecture, and if poorly managed, you could risk significant performance degradation.

The advice I can give you is not to exceed a size of 100G for the TABLESPACE hosting these objects. If you see a similar situation, consider restructuring the table with partitioning.

In the “LOB Segment Oracle Query” article, we provide a query that will help you identify the sizes of LOB objects.

The selected fields are:

OWNER – Object owner
TABLE_NAME – Name of the table associated with the LOB
SEGMENT_NAME – Name of the LOB segment
BYTES – Size of the LOB converted to MB

set lines 300
set pages 30
col owner for a15
col segment_name for a25
col segment_type for a25 
col TABLE_NAME for a30
col TABLESPACE_NAME for a20
select l.owner, l.table_name,l.segment_name, s.bytes/1024/1024 MB_ATT
from dba_lobs l, dba_segments s
where l.SEGMENT_NAME=s.segment_name
and s.segment_name='&lob_segment'
group by l.owner,l.table_name,l.segment_name,s.bytes order by 3 desc;

To view my guides on CRS Commands click here!
At this LINK, instead, you’ll find the SQL queries.

Stay updated on our activities by joining our community:
Facebook
Youtube
Linkedin

• Oracle Table LOB Query
• LOB Segment Oracle Query