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;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.