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;