Oracle Table LOB Query
With the article “Oracle Table LOB Query,” we provide a query that allows you to see the size of LOBs in a table.
In the Oracle database, LOBs are large object objects.
LOBs are divided into 2 categories: CLOB and BLOB:
CLOB – Character Large Object BLOB – Binary Large Object
CLOB can accommodate large text files, such as PDF, Word, and Excel documents. BLOB can accommodate large binary files and is compatible with videos, audios, and images.
LOBs are connected to a table as a field but are external objects to the table.
Below, we detail how to measure the size of Large Objects with the Oracle LOB Query.
The selected fields are:
OWNER – Object owner SEGMENT_NAME – Name of the LOB segment TABLE_NAME – Name of the table associated with the LOB BYTES – Size of the LOB converted to MB
The fields are selected from the tables DBA_LOBS and DBA_SEGMENTS.
Finally, the tables are joined on the field l.SEGMENT_NAME = s.segment_name, and we filter the search by the name of the table of interest.
Oracle Table LOB Query
set lines 180
col owner for a30
col segment_name for a45
col table_name for a45
select l.owner, l.SEGMENT_NAME, l.table_name, s.bytes/1024/1024 MB_ATT
from dba_lobs l, dba_segments s
where l.SEGMENT_NAME=s.segment_name
and l.table_name ='&table_name';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
0 Comments