Tag Archives: check lob object

Find Table Name for LOB objects segment in Oracle

Find Table Name for the LOB objects segment in Oracle

On checking the large objects in the database we find the LOB segment is consuming lot of space in the Datafiles.

Following are the steps to find LOB segment belong to which table name

1. Check the large size of segment present in the database

col owner for a6
col segment_name for a26
select * from
(select owner,segment_name||'~'||partition_name segment_name,segment_type,bytes/(1024*1024) size_m
from dba_segments
ORDER BY BLOCKS desc) where rownum < 11;

OWNER  SEGMENT_NAME               SEGMENT_TYPE           SIZE_M
------ -------------------------- ------------------ ----------
HR     EMP2~                      TABLE                    7297
HR     EMPLOYEE_BKP1~             TABLE                     624
XDB    SYS_LOB0000014380C00025$$~ LOBSEGMENT             57.125
SYS    IDL_UB1$~                  TABLE                      55
SYS    SOURCE$~                   TABLE                      45

2. Find the LOB belong to which tablespace

select e.owner,l.table_name,l.segment_name
from dba_extents e, dba_lobs l
where e.owner = l.owner
and e.segment_name = l.segment_name
and e.segment_type = 'LOBSEGMENT'
and l.segment_name like 'SYS_LOB0000014380C00025$$';

OWNER  TABLE_NAME        SEGMENT_NAME
------ ----------------- --------------------------
XDB    XDB$RESOURCE      SYS_LOB0000014380C00025$$
XDB    XDB$RESOURCE      SYS_LOB0000014380C00025$$
XDB    XDB$RESOURCE      SYS_LOB0000014380C00025$$
XDB    XDB$RESOURCE      SYS_LOB0000014380C00025$$
XDB    XDB$RESOURCE      SYS_LOB0000014380C00025$$
XDB    XDB$RESOURCE      SYS_LOB0000014380C00025$$

3. Get the list of LOB objects present in database

select owner, table_name, column_name, segment_name, index_name from dba_lobs;